DB connection problem in attemp to load DataGrid from SQLServer DB

Discussion in 'ASP .Net Web Controls' started by Richard Lionheart, Sep 14, 2005.

  1. 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
    Richard Lionheart, Sep 14, 2005
    #1
    1. Advertising

  2. Richard Lionheart

    Dan Guzman Guest

    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.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    "Richard Lionheart" <> wrote in message
    news:...
    > 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, Sep 14, 2005
    #2
    1. Advertising

  3. 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, Sep 14, 2005
    #3
  4. My current settings are "SQL Server and Windows NT".

    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
    Richard Lionheart, Sep 14, 2005
    #4
  5. Richard Lionheart

    Dan Guzman Guest

    > 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.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    "Richard Lionheart" <> wrote in message
    news:%...
    > 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
    >
    Dan Guzman, Sep 14, 2005
    #5
  6. 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
    ============
    Richard Lionheart, Sep 14, 2005
    #6
  7. Richard Lionheart

    Dan Guzman Guest

    > 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'

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    "Richard Lionheart" <> wrote in message
    news:...
    > 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, Sep 14, 2005
    #7
  8. I missed one "little" thing - Thanks!!!!

    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



    "Dan Guzman" <> wrote in message
    news:...
    >> 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'
    >
    > --
    > Hope this helps.
    >
    > Dan Guzman
    > SQL Server MVP
    >
    > "Richard Lionheart" <> wrote in message
    > news:...
    >> 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
    >> ============
    >>
    >>
    >>
    >>

    >
    >
    Richard Lionheart, Sep 14, 2005
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chris Snyder
    Replies:
    1
    Views:
    594
    Nicole Calinoiu
    Aug 23, 2004
  2. peshekeedweller

    sqlserver connection - need help desperately!

    peshekeedweller, Sep 15, 2005, in forum: ASP .Net
    Replies:
    16
    Views:
    618
    Juan T. Llibre
    Sep 18, 2005
  3. OD
    Replies:
    2
    Views:
    1,220
  4. =?Utf-8?B?SmVmZnJleQ==?=

    Upgrading ASP w/SQLserver 2000 to ASP.NET w/ SQLserver 2005

    =?Utf-8?B?SmVmZnJleQ==?=, Apr 6, 2007, in forum: ASP .Net
    Replies:
    2
    Views:
    459
    sloan
    Apr 27, 2007
  5. Replies:
    0
    Views:
    2,375
Loading...

Share This Page