DB connection problem in attemp to load DataGrid from SQLServer DB

[Richard Lionheart]

Hi All,

I originally (and mistakenly) posted this on
microsoft.public.dotnet.framework.windowsforms I hope this time I've posted
it on more appropriate newsgroups.

I'm trying to run Jeff Prosise's DataGrid example in Chapter 6 of
"Programming Microsoft .NET".

Jeff provided a MyComics.sql file which I've used in Query Analyzer to
create and populate a MyComics database in SQLServer 7.0 installed with NT
authentication. I verified that the table had been loaded correctly with a
Select statement.

IE displayed the following info:

============================
Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'sa'. Reason: Not associated with a trusted SQL Server connection.

Source Error:

Line 70:
"server=localhost;database=mycomics;uid=sa;pwd=xxxxx");
Line 71: DataSet ds = new DataSet ();
Line 72: adapter.Fill (ds);
Line 73: MyDataGrid.DataSource = ds;
Line 74: MyDataGrid.DataBind ();

Source File: f:\inetpub\wwwroot\DataGrid-Prosise.Comics.aspx Line: 72
================================

I exex out the password. I'm quite sure the password I had supplied was
correct for the "sa" account.

I suspect the problem is a conflict between my specification of NT
authentication and the example's use of the "sa" user. I also tried
removing the uid/pswd to no effect.

Any ideas? Should I post this stuff in some security NG instead?

Thanks in advance,
Richard

 

[Dan Guzman]

It looks like your SQL Server is configured to accept only trusted
connections. You can either change it to allow both SQL Server and Windows
or change your connection string to specify Windows authentication
('Integrated Security=SSPI' instead of userid and password).

Be sure not to use the 'sa' login for routine application access.

 

[Richard Lionheart]

Hi Dan,

('Integrated Security=SSPI' instead of userid and password).

Great! I stubled acoss that while Googling. I appended it but left
uid/pswd in. Now I've removed the latter. That got me further with a new
symptom.

============
Login failed for user 'ASCI2G-WS2\ASPNET'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'ASCI2G-WS2\ASPNET'.

Source Error:


Line 73: "server=ASCI2G-WS2;database=mycomics;Integrated
Security=SSPI");
Line 74: DataSet ds = new DataSet ();
Line 75: adapter.Fill (ds);
Line 76: MyDataGrid.DataSource = ds;
Line 77: MyDataGrid.DataBind ();


Source File: f:\inetpub\wwwroot\DataGrid-Prosise.Comics.aspx Line: 75

============

So I tried two things:

1. Added ASPNET as a login with no server roles but database access to
MyComics in roles public & db_datareader/writer.

2. I addition, I set security to *both* NT and SQLServer, but I just went
back to Ent. Mgr and can't figure out where I made that change. BTW, I'm
running SQLServer 7.0/SP2 over WinXP-Pro/SP2 with WindowsUpdate stuff.

If you have the time to tell me the path in Ent. Mgr to change the security
mode, I'd be very appreciative.

Be sure not to use the 'sa' login for routine application access.

OK, thanks.

Hope this helps.

It ABSOLUTELY helped!

Regards,
Richard

 

[Richard Lionheart]

Hi Dan,

I found where I made those security settings: I right-clicked the
server-name. My current settings are "SQL Server and Windows NT".

Sorry about cluttering the last post with a note/question about that.

Regards,
Richard

 

[Dan Guzman]

If you have the time to tell me the path in Ent. Mgr to change the

security mode, I'd be very appreciative.

Right-click on the server in EM, select properties and then click the
Security tab.

 

[Richard Lionheart]

Hi Dan,

Right-click on the server in EM, select properties and then click the
Security tab.

Thanks. But I've still got my main connection problem, which is echoed
below from the previous post. If you have the time, I'd appreciate more
guidance about how to get this to work.

I tried two things to get around the new symptom I got, which is displayed
below:

1. Added ASPNET as a login with no server roles but database access to
MyComics in roles public & db_datareader/writer.

2. I addition, I set security to *both* NT and SQLServer.

============
Login failed for user 'ASCI2G-WS2\ASPNET'.

Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'ASCI2G-WS2\ASPNET'.

Source Error:

Line 73: "server=ASCI2G-WS2;database=mycomics;Integrated
Security=SSPI");
Line 74: DataSet ds = new DataSet ();
Line 75: adapter.Fill (ds);
Line 76: MyDataGrid.DataSource = ds;
Line 77: MyDataGrid.DataBind ();

Source File: f:\inetpub\wwwroot\DataGrid-Prosise.Comics.aspx Line: 75
============

 

[Dan Guzman]

1. Added ASPNET as a login with no server roles but database access to

MyComics in roles public & db_datareader/writer.

Did you grant the 'ASCI2G-WS2\ASPNET' account access or did you create a new
SQL login named 'ASPNET'? I would expect that granting the
'ASCI2G-WS2\ASPNET' Windows account using a script like the one below will
address the 'login failed' error, as long as IIS and SQL server are running
on the same box:

USE MyDatabase
EXEC sp_grantlogin 'ASCI2G-WS2\ASPNET'
EXEC sp_grantdbaccess 'ASCI2G-WS2\ASPNET'
EXEC sp_addrolemember 'db_datareader', 'ASCI2G-WS2\ASPNET'
EXEC sp_addrolemember 'db_datawriter', 'ASCI2G-WS2\ASPNET'

 

[Richard Lionheart]

Hi Dan,

I had done everything your script does through EM. Or do I thought. I
double-checked it today before running your script on my (Prosise's)
database. Still didn't worked. So I swallowed my pride and ran your script.
Viola! The .aspx program ran perfectly.

What did I miss? I didn't prefix ASPNET with the domain! Grrrr!

Thank you very much for your excellent guidance.

Richard
(e-mail address removed)