Log on server error

Hi all i am using crystal report with asp .net.

i am using report files already created an configured with a dsn.

when i try to view the report using crystal report viewer in a web form i am getting the error

"CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed."

i am using

<code>
ConnectionInfo cnnInfo = new ConnectionInfo ( ) ;

cnnInfo.ServerName = strConnInformation[0] ;
cnnInfo.DatabaseName = strConnInformation[1] ;
cnnInfo.UserID = strConnInformation[2] ;
cnnInfo.Password = strConnInformation[3] ;

ReportDocument rptDoc = new ReportDocument ( ) ;
rptDoc.Load ( Server.MapPath ( "..//Reports//" + reportFileName ),
OpenReportMethod.OpenReportByDefault ) ;

//CrystalDecisions.CrystalReports.Engine.Table table = rptDoc.Database.Tables[0] ;

CrystalDecisions.CrystalReports.Engine.Tables rptTables = rptDoc.Database.Tables ;
foreach ( CrystalDecisions.CrystalReports.Engine.Table tbl in rptTables )
{
tblLogOnInfo = tbl.LogOnInfo ;
tblLogOnInfo.ConnectionInfo = cnnInfo ;
tbl.ApplyLogOnInfo ( tblLogOnInfo ) ;

if ( tbl.Location.IndexOf ( "." ) > 0 )
{
tbl.Location = tbl.Location.Substring (
tbl.Location.LastIndexOf(".") + 1 ) ;
}
else
{
//tbl.Location = tbl.Location ;
}
}

</code>

its working fine when i try to execute a report file which configured direct to a database in another machine by specifying the server name, database name, user id and the password,

its showing the error only when i try to execute the report which configured using dsn. dsn is created in my machine and the actual database server is another one.

can any one help me in this matter as i have to use the reports with dsn only in my application.

i got the informations from the net that its same for both type of connectivity to use the ConnectionInfo object and the TableLogonInfo objects.

what my be the problem with me.

is its related to any permision issues.Hai Vinod,

Even i faced the same problem with my C# and Access .
But this prblem arised only when i have a subreport in my main report.

Do u have any subreports in ur appli...If so u have to provide login information to those reports also.

regards,
Kolluru.|||Thanks for your reply kolluru. i will try this and will get back :)|||Do u have any subreports in ur appli...If so u have to provide login information to those reports also.

Im having the same problem and I have subreports. How do you provide the login details for the subreports?sql

Log In Confusion (newbie)

Greetings Gentlemen, this is my first post - I'm sure it will be one of many!

I'm a fairly experienced programmer, VB and ASP being my languages of choice. However, due to the cost of SQL Server, I have only recently been given an opportunity to use it - through work...

I have to say that I'm REALLY impressed with it - using it seems mainly intuituve - you have have a basic understanding of databases, then, it seems, that one can lever basic functionality out of SQL Server with the minimum of fuss and effort... My collegue, next to me, is using Oracle for another project, and he is constantly expressing his envy at SQL Servers rich GUI interface! Anyway, any more of this and you'll be thinking I work for Microsoft!

I am designing a web site, the back end of which will be SQL Server based. I am slightly worried about exposing the connection information to the database in the DSN connect string - at the moment, I am developing with the 'sa' login, which, I think is bad news... I need to use a login that is restricted to the web sites database, rather than giving access to the entire SQL Server.

So, I opened up the tree for my database, (I'm using SQL Server 2000) and opened logins, and created a new login. I made sure it used SQL Authentication, and I gave it default access to my database. In the 'Roles' tab, I made sure it was NOT a member of any server roles, since I don't want people administering the server through this login. All databases (like northwind etc) were de-selected, except of course for my database. I also made sure that the only permissions it had were databasereader and databasewriter.

After doing this, I logged off, and logged back on again (I have set it up to use SQL Server Authentication), and logged in using my newly created login.

I was expecting to be taken straight to my database only, but I could see ALL of the databases.

Further, when I open up the northwind database, and select a table (return all rows) it asks me for a password (fair enough), but if I enter the login I created for my OTHER database, it returns all the rows! Surely I don't have the correct permissions to do this?

I am missing something out?

Any comments would be appreciated.By default you are member of public group( which includes obviously any new logins ), which has Select ( read ) access to all system tables & views in all databases. This is normal.

By the way, I would STRONGLY recommend you add yourself to the db_owners role in the database as it gives you automatically full access to all tables, views etc in that database ONLY, and by the way you dont have to add yourslef into any read/write roles even though as db_owner your permissions are not explicitly displayed...

Yes, you can see all the databases - see my comment about public group. This is normal when using EM or Query Analyser.

Also, even though you are in db_owners, to ensure trouble free developing, ensure ALL objects created are owned by DBO. This makes sure no broken ownership chains exists. This also avoids the DBA hassling you for this....it also means if you have to transfer databases later on, as dbo exists by default on every database, it works straight up immediatley after transfer.

Hope this helps & post back if problems.

J.|||Thanks for your reply, jbleen.

I must admit, it sounds a little confusing!

Do you think you could guide me through adding a user, and 'locking' it down so that it can only access ONE database?

The reason for this is that the connect string to the database will have to be passed to the server box as part of an ASP script - therefore publicly advertising the usrname and password if anyone gets the source-code.

If the web application logs in with sa permissions, it's BAD! A hacker could then use ADO etc to create a command set that simply DROPS all databases! Ouch!!

In the meatime, I will print out your notes and have a good play with it! I'm really enjoying it, in a geeky, nerdy dont-let-my-mates-know kind of way! :D

Mark.|||Hi

Glad you are learning. Yep, using sa for login is an executable ( shot at dawn ) sort of thing. Dont do it - ever.

Easiest way to stop access by Public group is to not grant it permissions in the first place. If you have Public with access to system tables - not user created tables - etc ( which exists by default in every database when its created ) you can explicitly deny access to these. This way, only the user/s you define in the database can access that databse. Also make sure there no users have sneaked into any db_XXXXX database roles ( good housekeeping ).

Post back if probs.

J.|||Hi mark wills

Actually what is happening with your security issue is that when you access a database which you don't have rights to but the GUEST account exists then by default you have access.

If you look into the model database you should see a user account called guest, this is a built in user account. When you create a new database, SQL Server takes a copy of model as a starting point, therefore the account guest gets copied into your new database. As well guest is a member of public, all new accounts are, like jbleen100 had stated and by default public has read access again like jbleen100 had stated. The quickest and easiest action to do is remove guest from your database and you should also remove guest from the model database. By doing so you will eliminate this open access to all new databases.|||Good point Mark

My apologies...we already automatically remove the guest account from every new db by default, but if you were new to SQL you may not have picked that up.

Cheers

J.|||Thanks guys - you rock!

My next question(s) arise from creating an APPLICATION login/role. Theses are apparantly exactly what I need for my web application - I could then just use the sa login when working within the SQL Server console, and my web app could use an Application login, (with SELECT, INSERT, UPDATE, and DELETE permissions - but ONLY for my chosen database!) I should prpbably make that a seperate post, but I will check the online help first (very good online help in the Enterprise edition!)

Phew! It's confusing all this security stuff - the actual database stuff doesn't seem too bad, although of course datawharehousing etc is probably a subject in itself... but this security thing... :confused:

Thanks again guys...

Mark.sql

Log File Size reduction

Hi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
[vbcol=seagreen]
> Tomasz B.
> "Sathian" wrote:
>
and[vbcol=seagreen]|||Dear All,
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
Sathian|||Hi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>

Log File Filling Up

Hi All,
You may have seen my earlier post about an SQL log file filling up in
the space of 1 hour on a small ASP based website...
http://groups.google.co.uk/group/mi...0d4affac1291ed1
Since this post, I have been in touch with our hosting company with
some questions. Heres the answers:

> - What recovery mode is the database set to?
You database is set ti simple recovery mode.

> - How often is the DB backed up? When?
It is backed up nightly around 11.30pm

> - Is trucate log on check point switched on?
Yes it is turned on.
Given the above, what should I be looking for in my code to prevent the
log file filling up?
Surely if "truncate log on check point" is on, then the log should
never fill up?
Is a 'checkpoint' an explicit or implicit operation? i.e. Do I need to
use specific code following an update/insert/delete to cause a
checkpoint, and therefore a log file truncate?
Should I be using something like this around each operation that causes
a data change?...
BEGIN TRANS
--Some SQL
GO
Also, we do have some user SP's that do alot of updating - Should I be
using locks on these? If I did, would that cause the rest of the site
to stop working?
Any help will be much appreciated.
Simon.The simple answer is to do as little updating in a single transaction as
possible and keep your log size sufficiently large to accommodate your
largest transaction plus a little bit more as a buffer. Don't update a
billion rows in one fell swoop. Don't do a BEGIN TRAN and then run 100's of
INSERT/UPDATE/DELETE statements before you commit.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<adotek@.adotek.co.uk> wrote in message
news:1151945419.675236.308850@.h44g2000cwa.googlegroups.com...
Hi All,
You may have seen my earlier post about an SQL log file filling up in
the space of 1 hour on a small ASP based website...
http://groups.google.co.uk/group/mi...0d4affac1291ed1
Since this post, I have been in touch with our hosting company with
some questions. Heres the answers:

> - What recovery mode is the database set to?
You database is set ti simple recovery mode.

> - How often is the DB backed up? When?
It is backed up nightly around 11.30pm

> - Is trucate log on check point switched on?
Yes it is turned on.
Given the above, what should I be looking for in my code to prevent the
log file filling up?
Surely if "truncate log on check point" is on, then the log should
never fill up?
Is a 'checkpoint' an explicit or implicit operation? i.e. Do I need to
use specific code following an update/insert/delete to cause a
checkpoint, and therefore a log file truncate?
Should I be using something like this around each operation that causes
a data change?...
BEGIN TRANS
--Some SQL
GO
Also, we do have some user SP's that do alot of updating - Should I be
using locks on these? If I did, would that cause the rest of the site
to stop working?
Any help will be much appreciated.
Simon.

Log File Filling Up

Hi All,
You may have seen my earlier post about an SQL log file filling up in
the space of 1 hour on a small ASP based website...
http://groups.google.co.uk/group/microsoft.public.sqlserver.server/browse_frm/thread/ac15d4d498acbcf0/70d4affac1291ed1?lnk=st&q=%22simon+harris%22+log+file+sql&rnum=1&hl=en#70d4affac1291ed1
Since this post, I have been in touch with our hosting company with
some questions. Heres the answers:
> - What recovery mode is the database set to?
You database is set ti simple recovery mode.
> - How often is the DB backed up? When?
It is backed up nightly around 11.30pm
> - Is trucate log on check point switched on?
Yes it is turned on.
Given the above, what should I be looking for in my code to prevent the
log file filling up?
Surely if "truncate log on check point" is on, then the log should
never fill up?
Is a 'checkpoint' an explicit or implicit operation? i.e. Do I need to
use specific code following an update/insert/delete to cause a
checkpoint, and therefore a log file truncate?
Should I be using something like this around each operation that causes
a data change?...
BEGIN TRANS
--Some SQL
GO
Also, we do have some user SP's that do alot of updating - Should I be
using locks on these? If I did, would that cause the rest of the site
to stop working?
Any help will be much appreciated.
Simon.The simple answer is to do as little updating in a single transaction as
possible and keep your log size sufficiently large to accommodate your
largest transaction plus a little bit more as a buffer. Don't update a
billion rows in one fell swoop. Don't do a BEGIN TRAN and then run 100's of
INSERT/UPDATE/DELETE statements before you commit.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<adotek@.adotek.co.uk> wrote in message
news:1151945419.675236.308850@.h44g2000cwa.googlegroups.com...
Hi All,
You may have seen my earlier post about an SQL log file filling up in
the space of 1 hour on a small ASP based website...
http://groups.google.co.uk/group/microsoft.public.sqlserver.server/browse_frm/thread/ac15d4d498acbcf0/70d4affac1291ed1?lnk=st&q=%22simon+harris%22+log+file+sql&rnum=1&hl=en#70d4affac1291ed1
Since this post, I have been in touch with our hosting company with
some questions. Heres the answers:
> - What recovery mode is the database set to?
You database is set ti simple recovery mode.
> - How often is the DB backed up? When?
It is backed up nightly around 11.30pm
> - Is trucate log on check point switched on?
Yes it is turned on.
Given the above, what should I be looking for in my code to prevent the
log file filling up?
Surely if "truncate log on check point" is on, then the log should
never fill up?
Is a 'checkpoint' an explicit or implicit operation? i.e. Do I need to
use specific code following an update/insert/delete to cause a
checkpoint, and therefore a log file truncate?
Should I be using something like this around each operation that causes
a data change?...
BEGIN TRANS
--Some SQL
GO
Also, we do have some user SP's that do alot of updating - Should I be
using locks on these? If I did, would that cause the rest of the site
to stop working?
Any help will be much appreciated.
Simon.