Clicky

Hello, I need some assistance with connecting to a remote sql server via IIS application. Here is the setup:

1st Server (physical), VM Host, name: SQLServer03
Win Server 2008 R2, running SQL Server 2008 R2, running HyperV and also running a third-party accounting software

The instances on the SQL Server:
MSSQLSERVER (default)
ACCT_PKG (instance setup for the third-party accounting software)

Authentication is mixed mode, Allow remote connections to this server is enabled.

The SQL Services are setup with different credentials:

SQL Server service (db engine)for both instances are logged on as (a windows account, userX)
SQL Agent for both instances are logged on as (another windows account, userY)
SQL Server Browser is logged on as NT AUTHORITY\LOCAL SERVICE

2nd Server (VM), this VM client is running off the 1st Server, name: AcctServer02
Win Server 2008 R2, running IIS, and third-party accounting  web software
IIS Virtual Directory: ACCT_SRV
Connection Strings:
ACCT_SRV.My.MySettings.SQL.ConnStr
LocalSqlServer
SQLConnStr

All 3 have the same string:
Server= SQLServer03;Database= ACCT_PKG;User ID=sa;Password: (sql server sa password)

Here is some additional info:
I can login to the MS SQL Server Management Studio with both the windows and sa credentials.
I can connect to "SQLServer03" SQL Server from another remote SQL server, with both the windows and sa credentials.
I can establish a connection via "telnet IP 1433" from a remote desktop

Here are the issues I have:

The Accounting Web Application on the " AcctServer02" server gives the following error:
Cannot open database "ACCT_PKG" requested by the login. The login failed. Login failed for user 'sa'.

On the SQL Server Windows App Log, information event:
Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. [CLIENT: AcctServer02 IP]
source: MSSQLSERVER
Event ID: 18456

On the SQL Server Logs, " SQLServer03", the following error is logged:
Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. [CLIENT: AcctServer02 IP]
Error: 18456, Severity: 14, State: 8

I know this error means there is a password mismatch. but I have verified everything that I know of. I already reset the sa password and tested it locally and remotely (from another SQL server).

I don't know if the service accounts I setup are affecting this remote connection or if the connection string I am using is incorrect. I have verified the sa password in the connection string from the web.config file, as it is in plain text. Also, if I am trying to connect to instance " ACCT_PKG" why does the error log show the default instance, " MSSQLSERVER"?

Any help to resolve this issue will be greatly appreciated. Thank you.

asked 12/08/2011 12:17

ItSecurePro's gravatar image

ItSecurePro ♦♦


6 Answers:
Please have a look at couple of these articles:

1. http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456/
    Refer state 16 and 38 in this article

2. http://www.eraofdata.com/blog/sql-18456-login-failures/
link

answered

pateljitu's gravatar image

pateljitu

Wow! Those are great references. I'll report back my findings. Thank you.  
link

answered 2011-12-08 at 09:58:46

ItSecurePro's gravatar image

ItSecurePro

Here is my status. I updated the 2008 SQL Server to SP1. I did verify the ASP Connection Strings for
Accounting Web Application. Same results

The SQL Sever Log:

Login succeeded for user 'sa'. Connection made using SQL Server authentication. [CLIENT: AcctServer02 IP]

immediately followed by

Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. [CLIENT: AcctServer02 IP]
Error 18456, Severity 14, State 38.

I am still trying to troubleshoot this issue with the information provided. If anyone has additional info or any other tips I would really appreciate it.
link

answered 2011-12-08 at 12:56:49

ItSecurePro's gravatar image

ItSecurePro

Could you please provide with connection string that you are currently using.
link

answered 2011-12-09 at 06:51:53

pateljitu's gravatar image

pateljitu

Hello, the issue has been resolved. The problem was the connection string. I had the string pointed to the specified instance of SQL and not the actual DB in that instance.

Connection Strings:

ACCT_SRV.My.MySettings.SQL.ConnStr
LocalSqlServer (default)
SQLConnStr

 The Correct string
Server= SQLServer03\ ACCT_PKG;Database= ACCT_DB;User ID=sa;Password: (sql server sa
password)

So the first part is the server and the SQL instance (ACCT_PKG), and then actual DB.

Simple mistake. Thank pateljitu for the references.
link

answered 2011-12-09 at 09:02:59

ItSecurePro's gravatar image

ItSecurePro

This issue has been resolved.

link

answered 2011-12-13 at 12:29:32

ItSecurePro's gravatar image

ItSecurePro

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×1

Asked: 12/08/2011 12:17

Seen: 262 times

Last updated: 12/17/2011 05:20