Odd connection issue - (yes I'm running in mixed mode authentication)

  • Thread starter Thread starter Coder Mouse
  • Start date Start date
C

Coder Mouse

Here is the scenario...


- There are two sql servers: "Production" and "Staging".
PLEASE READ THIS BEFORE REPLYING --> Both SQL servers are set to "SQL
Server and Windows" mixed authentication mode. (I
have verified this multiple times). Both Sql Servers have Named pipes and
TCP/IP port 1433 installed under network client configuration

- There is a development machine: "Development".
Any machine we try to connect from using the following has the same
behavior however.

Problem: Cannot connect from ASP.NET app running on "Development" under the
IUSER account to the "Staging" server using SQL
Server authentication. Here is the connection string...

"Server=Staging; Database=V2Staging; User=sa; Password=development"

It throws the exception:
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied. at
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction) at
System.Data.SqlClient.SqlConnection.Open() at
SqlSecurityTest.WebForm1.Connect(String connectionString) in
c:\inetpub\wwwroot\sqlsecuritytest\default.aspx.cs:line 57 at
SqlSecurityTest.WebForm1.Page_Load(Object sender, EventArgs e)
in c:\inetpub\wwwroot\sqlsecuritytest\default.aspx.cs:line 36

Oddity 1: We CAN establish a connection from the same ASP.NET app on
"Development" running under the IUSER account to the
"Production" using SQL Server authentication.. Here is that connection
string...

"Server=Production; Database=V2Production; User=sa; Password=production"

Oddity 2: We CAN establish connections from the ASP.NET app on
"Development" running under the NT AUTHORITY\NETWORK SERVICE
account to BOTH servers using SQL Server authentication..


I have boiled this down to this question...
Why should the windows account the application is running under imapct
connectivity to a SQL Server at all when using SQL
Server authentication and both sql servers are running in mixed
authentication mode?

This has been a thorn in my side for many days. I want to solve this
problem and will set up a web page with screen shots or
collect any information you might find useful to help me solve this problem.


Thank you for your help,
-Thomas Kadlec
(e-mail address removed)
 
First, I would check if anything at all can connect to the staging server
given those credentials. I.e., is this specifically an Asp.Net problem, or
is nothing capable of using these credentials to connect successfully? You
could try connecting from regular old ASP, and also write a little windows
or console app that just creates a connection.

If nothing can connect, then clearly this has nothing to do with ASP.NET,
and the problem lies elsewhere entirely.

If everything can connect except ASP.NET, you might want to open up a case
with microsoft, as since you've posted this same questions many times
already, no one else knows what the problem is.
 
Hi Thomas,

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 you have two servers running similar
SQL servers, your ASP.NET application can connect to one server, but was
unable to connection to the other. And you're connecting both servers with
SQL server authentication. If there is any misunderstanding, please feel
free to let me know.

Marina has provided us with a good suggestion. We can use SQL profiler to
check with which credential, the ASP.NET app is 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, please verify if
this user name and password is a valid one. Please also try the following
connection string to see if it works.

Provider=SQLOLEDB.1;Password=development;Persist Security Info=True;User
ID=sa;Initial Catalog=V2Staging;Data Source=Staging

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."
 
First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you have two servers running similar
SQL servers, your ASP.NET application can connect to one server, but was
unable to connection to the other. And you're connecting both servers with
SQL server authentication. If there is any misunderstanding, please feel
free to let me know.
Click to expand...

Thank you for your response,

Your description is close, however as I listed in "Oddity 2" I can connect
to both servers provided the ASP.NET app if I don't use the IUSR account.

It's only when the process is running under the IUSR account with
impersonation=true in the web.config. I have no problems connecting to the
"Staging" server under a console app or sql query analyzer or even an
asp.net app on the same machine as long as the asp.net app isn't running
under the IUSR account... yet I *can* connect to the "production" sql server
when same asp.net app is running under the IUSR account.

I will gather the information Marina suggested and then create a web page
showing side by side configuration and all the scenarios I mentioned above.
It is all very odd. Every competent developer friend I've shown this too
just scratches their head and says "that's weird!". Hopefully there is just
some small thing I am overlooking and getting it all out on a web page will
reveal the answer to someone.


-Thomas Kadlec
 
Hi Thomas,

If you are using impersonation in the app, it is recommended to turn of the
anonymous access. Or the impersonated credential cannot be passed to the
SQL server when using windows integrated security.

You can also try to compare the two database servers. If you have IIS
installed on same server, please check the order of enabled protocol in SQL
Server Network utility. IUSer may have permission to use TCP/IP but not
with pipe. You can check the order in Start -> Microsoft SQL Server ->
Server Network Utility.

If any thing 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."
 
Back
Top