Problem with SQL Server

[David W. Simmonds]

I have an asp.net form that works fine with an Access database. Now I want
to use Sql Server 2000. It is installed on the same Windows 2003 machine as
the aspx code runs. I cannot seem to have a SqlConnection successfully
connect. I use the following:

string str = "Persist Security Info=False;Integrated
Security=SSPI;database="+strDatabase+";server="+strServer+";Connect
Timeout=30";
m_SqlConnection = new SqlConnection(str);
m_SqlConnection.Open();

I get Login failed for user 'NT AUTHORITY\NETWORK SERVICE'

I have also tried to leave out the Persist Security Info and Integrated
Security, and replace it with uid=sa;pwd=<my sa password here> but I get the
error:

Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server
connection.

The same thing occurs for any user that I add to the Enterprise Manager. At
this point I am not sure what I need to do to configure Sql Server. I know
that there are many knowledgable Sql Server users out there. Can anyone
answer my question?

 

[Kevin Yu [MSFT]]

Hi David,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that your ASP.NET app failed to connect to
the SQL server whether with integrated security or with SQL server
authentication. If there is any misunderstanding, please feel free to let
me know.

Based on my experience, this might be caused by many reasons. As ASP.NET
working process is running under an account which might not have the
permission to access the SQL server, we have to change this account. Here
are the steps:

1. Open Windows Explorer and navigate to
%windir%\Microsoft.NET\Framework\v1.1.4322\CONFIG (assuming that you're
wording on .NET framework 1.1)
2. Open machine.config with Notepad.
3. Find the processModel tag. It might be look like the following:

<processModel enable="true" timeout="Infinite" idleTimeout="Infinite"
shutdownTimeout="0:00:05" requestLimit="Infinite" requestQueueLimit="5000"
restartQueueLimit="10" memoryLimit="60" webGarden="false"
cpuMask="0xffffffff" userName="MACHINE" password="AutoGenerate"
logLevel="Errors" clientConnectedCheck="0:00:05"
comAuthenticationLevel="Connect" comImpersonationLevel="Impersonate"
responseDeadlockInterval="00:03:00" maxWorkerThreads="20"
maxIoThreads="20"/>

4. Notice that the userName for the working process is "MACHINE", we can
change it to "SYSTEM". The system account might have higher permission to
access SQL server. Save the file after modifying.
5. Click Start -> Run to open Run window. Type "iisreset" and press enter.
(Quotation marks are not included.) This will reset the IIS server. After
the IIS server is reseted, please try to connect again with windows
integrated authentication.

If you need to connect to the SQL server installed on the same computer
with SQL authentication, please try the following connection string:

Provider=SQLOLEDB.1;Password=<your password for sa>;Persist Security
Info=True;User ID=sa;Initial Catalog=<your database name>;Data
Source=(local)

If the two methods still doesn't work, I think we can use SQL profiler to
check with which credential, is the ASP.NET app connecting to SQL server.
Here are the steps:

1. Click Start -> Programs -> Microsoft SQL Server -> Profiler to open SQL
profiler.
2. Select File -> New -> Trace to create a new trace.
3. Enter the server name, user name and password. Click OK.
4. Click Run to start the trace.
5. Start your ASP.NET app and repro the error.

When you can see the credential used to access SQL server in the trace log,
please verify if this user name and password is a valid one.

If the problem still persists, please feel free to let me know. I am
standing by to be of assistance.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[David W. Simmonds]

The first two steps did not help. I got nothing from the SQL trace. All that
appeared was Trace Start and Trace Stop. Now what?

 

[Kevin Yu [MSFT]]

Hi David

Since you got nothing from the SQL trace, there might be something wrong
with the Trace Properties. When you are creating a new trace, before Click
the Run button, please check the Events tab and Filters tab. Make sure that
Security Audit / Audit Login and Audit Logout has been added to the right
tree view in Events tab. And ApplicationName/ Like property has not been
set. So that the profiler can trace the actions performed by your
application.

If you are working in a domain environment, please also try to impersonate
a domain user which is the administrator of both the web server and SQL
server. Generally, this will work in most cases. If the problem still
persists, would you please paste the trace log here?

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[David W. Simmonds]

I am running the SQL Profiler on the machine that is running SQL Server. I
am running an ASP.NET form from a webpage in a browser on another machine.

The Filters are set as default and are like you suggest. The Application
Name is empty. I added Audit Login Failed Filter. It says:

NTUserName is NETWORK SERVICE
LoginName is NT AUTHORITY\NETWORK SERVICE

I've attached the trace.

I still cannot connect.

 

[David W. Simmonds]

Apparently my sql server was not set to mixed mode authentication. Once I
set it to that mode, I can now supply a UID and PWD to the connect string
and all works fine.

 

[Kevin Yu [MSFT]]

Hi David,

I'm glad to know that you have had the problem resolved. However, I think
if you are installing the SQL server on the same machine as the web server,
the windows authentication mode has to work, since we have run ASP.NET
working process under SYSTEM account. Anyway, it's good to know that your
program works fine now.

If you have any further questions, please feel free to let me know.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."