Monday, March 19, 2012

error: 17194

I cannot use sql connection. This is my connection string

'Conn.Open "Provider=SQLNCLI;Database=dbname;Server=.\\MSSQLSERVER;UID=username;PWD=test123;"

The error message appears on browser is

Microsoft SQL Native Client error '80004005'

Client unable to establish connection due to prelogin failure

/Connect.asp, line 8

in SQL server ERRORLOG, it shows
Error: 17194, Severity: 16, State: 1.
The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications....

How can I fix this error?


Are you using SQL Express DB? Normally, if u did install it with default settings the instance name is COMPUTERNAME\SQLEXPRESS

Second, assuming that your Server=.\\MSSQLSERVER is correct. Go to Management Studio and check if the UID has permission to login.

Third, check your Provider=SQLNCLI; if this is correct.



|||In SSCM, it shows
SQL Server 2005 services:
- SQL Server Browser

So, I think the instance name is MSSQLSERVER

About the login, I am using Windows Authentication.
- created an account in Computer Management
- add that account in SQL Express DB by Management Studio

what else do I need to do?

about the connection, I am using OLEDB, so I chose Provider=SQLNCLI. How can I check if it's error or not?


|||One thing I have noticed: when going to command promt, use this command
sqlcmd -S <servername> <<< this command is working

sqlcmd -S <servername>\MSSQLSERVER <<< this is not working. the error message is

"HResult 0x57, Level 16, State 1
SQL Network Interfaces: Connection string is not valid [87].
Sqlcmd: Error: Microsoft SQL Native Client: An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Sqlcmd: Error: Microsoft SQL native Client : Login timeout expired"

Followed the below help without any luck;en-us;914277


Since you have done the KB atricle;en-us;914277 try to restart your workstation.

In your workstation, are you only using SQL Express 2005 or their is an existing MSDE 2000 or SQL 2000 ?

Go To SQL Server Configuration Manager make sure TCP-IP is enabled

Again go management studio--> Security-> Logins --> go to your windows user added previously and invoke its properties go to user mappnig to have acces on the database

Go to management studio --> security--> logins --> go to your windows user added previously and invoke its properties go to status and choose login--> enabled


I have switched authentication mode to mixed mode, then add a new sql user, and the db is working fine :) Not sure why windows authentication mode is not working, even I have done all above steps :(

anyways, thanks a lot for your time :)



Hi again...

Could you try this connection string

Data Source=SQLEXPRESSINSTANCENAME;database=YOURDATABASENAME;Integrated Security=True;

I guess this work this time. In my understanding using UID and PWD is for SQL user.

To check if the problem is really in your connection string or SQL Express, try to go to management studio and choose windows authentication mode if you can get through.

P.S. Hung Pham mind if i asked your msn id so that i can add you.



No comments:

Post a Comment