Cannot authenticate to sql server through asp.net project upon moving project to 2003 server.

Dave,
   I am pretty new to .net.  Is this the user that you set in IIS for the app to run as?  I put it on a user with admin rights just to see if I could get it to work that way.  Still get the error.
   Thanks,
      ~~Kim

Yes.

The UserID *must* be a member of the IIS_WPG group for the applciation pool to start.
Additionally, the UserID must have appropriate SPNs set for Kerberos Authentication Delegation to work properly.

What I would suggest is to change the Application Pool to use Network Service for the time being - this will allow it to use the auto generated SPNs of the server.

In your question you said the dev server was trusted for delegation.  Is the live server trusted as well?  This will not work if it isn't.

Dave Dietz

What is the IIS_WPG Group?  I didnt do anything with that on the development server?
I have SPN's set for the sql server, but didnt know had to do that with the user as well?  How can I do that?

Also, dont know how to set app pool to use Network service...?

The live server IS trusted for delegation.

Thanks for helping.

~~Kim

can anyone give me a little more details on this?

The IIS_WPG group is a group that is given proper permissions to particular resources to be able to be used as the identity of an Application Pool.

By default System, Local Service, Network Service and IWAM_<machinename> are in this group.  If you want to run the app pool as a user not included here you must add them to the group.

To set an SPN for a user you use the SetSPN utility with a command line similar to the following:

 SetSPN -A http/hostheadername domain\userid

where hostheadername is the name you use to access the website and domain\userid is the doamin and name of the account the application pool runs as.

To set the AppPool to run as Network Service you simply open the properties on the AppPool, go to the identity tab, select the Predefined radio button and select Network Service on the dropdown.

Enoguh details for now? :-)

Dave Dietz

Thank you - I thought I was making progress, but now I'm not.  I found an article that showed how to give the IIS_WPG group permission on SQL.  I did that yesterday before going home.  Now today, that group is no longer listed under Security in SQL - and when I try to re-add it, it tells me the group does not exist.

The program IS running under the default application pool.

What am I doing wrong??

OK - Yesterday I added to SQL networkdomain\iis_wpg
It wont let me add IIS_server\iis_wpg b/c it says it does not exist.  Ican browse and select it using the ellipse button.  Just when I try to save it wont let me.  

I have gone through each step listed on this page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskAccessingSQLServerUsingWindowsIntegratedSecurity.asp

* IIS Directory Security is listed as Windows Integrated.  Anonymous Access is not enabled.
* Web.Config file includes the following :
   <authentication mode="Windows">
   </authentication>
    <authorization>
        <allow users="*" />
   </authorization>
   <identity impersonate="true" />
* Web.Config also includes connection string :
     <appSettings>  
      <add key="ConnectionString" value="data source=Sql-Server;Trusted_Connection=Yes;initial catalog=Northwind;integrated security=SSPI;persist security info=False;workstation id=Web-Server;packet size=4096;Network Library=dbmssocn"/>
    </appSettings>
*   Web Server is trusted for Kerberos Delegation
*   Application is running in the Default App Pool as Network Service

The only thing I didn't fully understand in the article was configuring SQL Server...

"To configure SQL Server for Windows integrated security
From the Windows Start menu, choose Microsoft SQL Server, and then choose Enterprise Manager.
Open the node for the server and expand the node for the database you want to give users permissions for.
Right-click the Users node and choose New Database User.
In the Database User Properties dialog box, enter domain\username in the Login name box, and then click OK. Alternatively, configure the SQL Server to allow all domain users to access the database. "

I didnt do anything with this for the development site, but went ahead and added "domain/primary-group" to have permissions to the database in SQL.

After all of this, I still get the "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." error.

SQL Server is Windows 2003 Server running SQL 2000
Web Server is Windows 2003 Server running IIS 6
Domain Controller with Active Directory is Windows 2003 Server
Application is written with .NET Framework 1.1 ASP.NET (VB)

The only difference between the working development site and the non working live site is the development site uses a Windows 2000 Professional machine as a "web server".

This doesn't seem like it should be overly complicated to set up.  After days of researching, checking, trying different things, I find it pretty frustrating that still nothing works.  I am completely at a loss.

Any suggestions or help, please??

Thanks,
   ~~Kim

also - i am under the impression that with the application running as Network Service, I dont have to set up any SPN's or do anything with the IIS_WPG group.  Is that correct?
  ~~Kim

Is SQL Server running as local system or as a specified user?

Dave Dietz

I dont know ..... where do you check this at?

Under Sql Server Properties, Security tab

Authentication selected is "SQL Server and Windows"
Start and run SQL server is "This Account".  

The domain/username there is added as a user to SQL Security that does have permission to the database I'm trying to get to.

Is that what you were asking?

I believe those settings are like that for another windows based application that is used company wide.

If it's a SQL issue, why is the error login failed for user "NULL" instead of my username?

Yes, that is what I was looking for.

Since SQL is not running as the System account it has to have an SPN registered for it for Kerberos to work properly.

The following article explains this:

811889 How to troubleshoot the "Cannot generate SSPI context" error message
http://support.microsoft.com/?id=811889

The reason you are seeing the user NULL is that when you try to use NTLM instead of Kerberos there are no credentials passed at all.

Dave Dietz

The user it's running as has full administrator rights - shouldn't it then be able to create it's own SPN?

Quote the article :
"If you test using a domain administrator account as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN are present."

Also, in an earlier post you said if the site uses Network Service, it would use automatic SPNs.. ?

The user it runs as would have to have Domain Admin rights since registering an SPN makes a modification to Active Directory.  Local Admins do not have the proper permissions to modify these AD settings.

The earlier comment regarding the Network Service account has to do with the SPNs on the *web* server.  Now I am thinking that the issue has more to do with the SPNs on the *SQL* server - both have to be set correctly for auth delegation to work.

Dave Dietz

After trying all of the above steps my question has still not been answered and a solution has not been found.

We can keep the question up and keep trying to find a solution or just delete it.  Whatever anyone would like to do is fine with me.

~~Kim

You set an SPN on the SQL Server?

Dave Dietz

No.  I do not know how to set the spns.  

I downloaded a setspn program from microsoft and followed their directions on how to do it http://support.microsoft.com/default.aspx?scid=kb;en-us;319723 on this article.  When i try to run any "SetSPN" commands through the command prompt I get an error that "setspn is not recognized as an internal or external command, program, or batch file."

SQL shouldn't be running as a local admin, if i'm looking in the right place in SQL, right click the server --> Properties --> "Start and Run SQL in the following account", the account there is the domain administrator acount, which from anything I've read it should have permissions to create its own SPNs .. ?

I even tried to put a plain .aspx page in that project that doesn't have any database access and I can't even access that.  I changed impersonation to false in web config to see what i would get and now i get login failed for user DOMAIN\WEBSERVER$ .

I still dont understand why SQL is the problem when it's the same SQL server that the application runs with and talks to when the application was on our development server.

You said i was getting login failed for user NULL b/c of using NTLM auth instead of Kerberos .... why is it using NTLM?