Thursday, March 29, 2012

Error when trying to log in to SQL server

I've just finished developing a web app, which works fine on my PC, running IIS locally. It uses windows authentication and a trusted connection to retrieve data from SQL server. So far so routine. When I deploy the app to my live server, it returns the error:

Login failed for user '\'

The stack trace seems to indicate that it's at the point where the app tries to retrieve data that the error occurs. I proved this by changing my connection string to explicitly define the log on, and it retrieved the data no problem.

I set up a very simple webform with a label on, to return what it thought the user's log on name was, and it returns the correct name.

Any ideas as to why the username isn't being correctly passed to SQL server?Have you made sure the Web Account is a valid user of the SQL Server? If the IIS box and the SQL box are different you have to ensure the user has network rights too.|||Yes, the user (me!) definitely has rights on the SQL server. How come it works locally on my PC, but not on the server?

What network rights do you think I should investigate?

Thanks.|||Sorry I mis-read your original post, I didn't notice that your site was using NT Authentication. So here is my mental checklist:
1. Web Server has access to the Domain server to authenticate the user
2. The Web Server isn't the Domain Server
3. SQL Server security logins knows about the Windows user/user group
4. The SQL Database knows about the Windows user/user group

I mentioned the network security because when you use anon web access the user must have network access to go across machines. That "shouldn't" be an issue here.

Also run SQL Profiler to see if the user is getting set to SQL and then going wrong. Sounds more like its a problem on the web server though, something wrong with the impersonation.|||I'm positive that the user is being authenticated properly on the app, because when I load a simple page with a label which displays the user name, it displays correctly. For some reason this user name isn't getting passed to SQL server correctly.

I tried deploying the application to the box which has the SQL server running, and gess what? It worked. So does this mean that the first box can't access the domain server? Any idea how I can check that?

I'll have a think, and let you know how I get on.

Cheers.|||Hang on, just cause you're using Web Authentication doesn't mean it will impersonate the user onto SQL Server. Do you set the principal security when the user connects?

Check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch03.asp

No comments:

Post a Comment