Solve SQL Server permissions and authentication problems

Special Ops Security

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:

Step 2 of 2:

SQL Server 2005 on a Member Server (2003)
I'm running Visual Studio .NET 2005 on my work station.
I can access the SQL Server data from within my .NET project.
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.
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

PRO+

Content

Find more PRO+ content and other member only offers, here.

E-Handbook

Pricing out the new Windows Azure SQL Database

Have a question for an expert?

Please add a title for your question

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

Meet all of our SQL Server experts

View all SQL Server questions and answers

Dig Deeper

E-Handbook

Related Q&A; from Steven Andres

Have a question for an expert?

0 comments

E-Mail

Username / Password

Password

Forgot Password?

Your password has been sent to: