Q

Solve SQL Server permissions and authentication problems

For the past two weeks I've been trying to resolve a permissions problem, an authentication problem, or both. I've run out of ideas of where to look. Here is what I have: (1) SQL Server 2005 on a Member Server (2003) (2) I'm running Visual Studio .NET 2005 on my work station. (3) I can access the SQL Server data from within my .NET project. (4) If I access the website from outside my .NET project, I get Login Failed for user NT_AUTHORITY/NETWORK SERVICE. This happens on any PC and even the member server that houses SQL and the Intranet site IIS. (5) The reason I think I have a permissions problem is because of the behavior of two seperate test projects. Project #1 uses PROFILES to save state. When the program gets to this line of code, the Login Failed message appears. In project #2, I'm not saving state. But when the code attempts to gather data into a dataset, the program halts with the Login Failed message. Can you give me specific instructions on where to look and what to try?

For the past two weeks I've been trying to resolve a permissions problem, an authentication problem, or both. I've run out of ideas of where to look. Here is what I have:

  1. SQL Server 2005 on a Member Server (2003)
  2. I'm running Visual Studio .NET 2005 on my work station.
  3. I can access the SQL Server data from within my .NET project.
  4. If I access the website from outside my .NET project, I get Login Failed for user NT_AUTHORITY/NETWORK SERVICE.
    This happens on any PC and even the member server that houses SQL and the Intranet site IIS.
  5. The reason I think I have a permissions problem is because of the behavior of two seperate test projects. Project #1 uses PROFILES to save state. When the program gets to this line of code, the Login Failed message appears. In project #2, I'm not saving state.But when the code attempts to gather data into a dataset, the program halts with the Login Failed message.

Can you give me specific instructions on where to look and what to try?

I would try to confirm it is in fact a permisisons issue by going to the IIS settings for the virtual site and deactivating Anonymous access. Enable either Integrated Windows access or Basic auth. Next, go to the website as usual. You will be prompted to login with an HTTP/1.0 old-style login popup. Use any account that you know has read/write permisisons to the directory where the site files are stored.

If everything works fine, then it is definitely an authentication issue. Now, go back to anonymous access and start adding permissions to the IUSR_SERVER account to see if you can get the application to write properly. I would also suggest (if possible) downloading the excellent FileMon tool from SysInternals and see which read/write accesses are being denied. That will help you hunt down the issue.

This was first published in March 2007

Dig deeper on SQL Server Security

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close