weird sql connection issue

[Thomas Kadlec]

Before I describe the problem let me try to eliminate any obvious issues...

- There are two sql servers: "Production" and "Staging".
- Both SQL servers are set to "SQL Server and Windows" authentication mode.
(I have verified this multiple times).
- There is a development machine: "Development". (although any machine we
try to connect from using the following has the same behavior)
- All of these machines are on the same local network.

The problem: We are unable to establish a connection from an ASP.NET app
running on the Development machine under the Development\IUSER_Development
account to the "Staging" server using SQL Server authentication. Here is
the connection string... (passwords have been changed to protect the
innocent.)

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

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 an ASP.NET app running on the
Development machine under the Development\IUSER_Development account to the
"Production" server SQL Server authentication.. Here is that connection
string...

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

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

Question 1: Why can we connect to one sql server running under the
Development\IUSER_Development account but not the other sql server?

Question 2: What is it about the NT AUTHORITY\NETWORK SERVICE account that
allows connections to both sql servers?

Question 3: When using SQL Server authentication what exactly is happening?
i.e. why should the windows account the application is running under effect
connectivity to a SQL Server at all?


Thank you for your help,
- Thomas Kadlec
(e-mail address removed)

 

[Cowboy \(Gregory A. Beamer\)]

There are a couple things I can think of right off hand.

1. You do not have the proper client connection libraries set up on the
staging server (but do on the production server).
2. The staging server is set up as an instance, not the default instance
3. You do not have mixed mode set up on the staging server

Of the three, I would say 3 is the most likely, with the symptoms you are
describing.

You can profile the Staging server and see how the ASP.NET app is
connecting. If you find windows auth, you have no mixed mode set up on the
staging server. You can also test this rather quickly on a client machine by
trying to connect with Query Analyzer with a UID and password.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[Thomas Kadlec]

Thank you for responding,

In response to your points...

Point 1: Both machines have TCP/IP port 1433 and Named Pipes - sql/query
under client connections

Point 2: Staging is definetly setup as the default instance.

Point 3: Staging is definetly set up in SQL Server and Windows
authentication mode (mixed mode). I can connect using query analyze and the
SA login with no problem to Staging.


-Thomas Kadlec