Test Connection Failed because of an error initializing provider

Article
01/29/2010

Question

_{Friday, January 29, 2010 6:03 PM}

I have a question as to why I cannot connect to my SQL Server. I am no SQL man so please bare with me. I was initially creating a sharepoint site and trying to configure a database but the Configuration Wizard would fail stating it could not find a user. I was then informed I could created a .UDL file to attempt to test my connection. That is where I am at. I can see the SQL server, and when I use Windows NT Integrated security, my connection is successful. When I use a specific user name or password, I cannot connect to it. The only connection that is successful is the sa user. Can anyone point me in the right direction as to where I should check my permission levels?

I checked on my server roles and security roles on SQL and the account that is being used to create the sharepoint site is a securityadmin, dbcreator, public and sysadmin.

Surface Area configuration is allowed to have remote connections for TCP and Named Pipes. I even ensured that DTC is allowed to have remote connections on both machines.

Any help would be greatly appreciated. Thanks!

All replies (14)

_{Wednesday, February 10, 2010 10:45 PM ✅Answered}

My mistake. The error was do to password change.

Any new SQL accounts I create will get a successful connection to view the database (once i changed the password).

It's just any account with the "domain\username" will get the failed attempt. Sounds like it's an Windows Integrated Security thing to me.

_{Friday, January 29, 2010 7:04 PM}

Can you post the error that occurs when you try to connect to SQL apart from sa login?
Is it a login failure or not able to connect error ?Thanks, Leks

_{Friday, January 29, 2010 8:30 PM}

Microsoft Data Link Error

Test Connection failed because of an error in initializing provider.

On the SQL server I have a Failure Audit in my application log

It is Event 18456 with this in the Event

_{Friday, January 29, 2010 11:10 PM}

Could you describe the exact steps?
Which provider do you choose?
Which user name?This posting is provided "AS IS" with no warranties, and confers no rights.

_{Monday, February 1, 2010 2:49 AM}

Hi,

Event 18456 is a general error while connecting to SQLServer. What you need to get is the state number of the error you faced.

Try the following blog to see how to get the state number and get the root cause according to the state-issue mapping table.

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Thanks.
Microsoft Online Community Support Please remember to mark the replies as answers if they help and unmark them if they provide no help.

_{Monday, February 1, 2010 5:09 PM}

Error: 18456, Severity: 14, State: 6.

That's what I am getting.

_{Monday, February 1, 2010 11:30 PM}

This state 6 occurs when a user try to login with a WINDOWS account but specifying that as a sql account with username and password .Remember that when you connect to a SQL server with windows authentication , you would have to use integrated security to be set to true instead of specifying windows account’s username and password .

Thanks, Leks

_{Tuesday, February 2, 2010 8:00 PM}

Where do I enable the integrated security? Is that in the Surface Area Configuration under the Reporting services? If so, it is set to true. My server authentication is both windows and SQL.

_{Tuesday, February 2, 2010 8:29 PM}

Check your IIS properties for this setting; i would think some settings are sending the connection

request for a windows authentication but by specifying it as a SQL authenticated login .

if you are using webconfig files to connect to the db server , then modify your connection string something like this

Server=myServerAddress; Database=myDataBase; Trusted_Connection=true;

This means that the account under which the windows runs and executes the connection strings come in to SQL server,

For windows accounts you cannot specify username and password in your connection string

The provision of username and password is only for SQL authenticated logins - example

Server=myServerAddress; Database=myDataBase; User ID=myUsername; Trusted_Connection=False;

Thanks, Leks

_{Thursday, February 4, 2010 6:21 PM}

I added a connection to the SQL server but still doesn't work. Initially I was trying to install MOSS 2007 and it failed. In the log it stated it couldn't find the user. I hit the Sharepoint forums and after troubleshooting with them it came to the conclusion I had an issue with SQL.

_{Friday, February 5, 2010 5:59 PM}

Can you post the new full Login Failed message from the ERRORLOG? We need to see the login failed State.

_{Thursday, November 11, 2010 9:57 PM}

Thanks a lot, this helped me too. But this was very weird.

_{Monday, June 13, 2011 5:18 PM}

I also face same issue in my 2008R2 ent server. I also face same issue here. Ok I briefly explain what has happen suddenly, Everythings working fine from local administrator account , but I login as domain admin and restart server then only start this issue I feel Now what is happening here when we try to access our third party database (we have only read access) using thire user Id and password it connect without any issue, but save my password option is not working (mean it will un tick and following error massage will come TITLE: Connection Manager Test connection failed because of an error in initializing provider. Login failed for user 'EnterpriseDBUserEnterpriseAdmin'. BUTTONS: OKMicrosoft TechNet Forum Bandara

_{Monday, June 20, 2011 6:08 PM}

Exactly what happen here Package configuration issue

ref below article done by Dinesh/MVP

http://www.sql-server-performance.com/2007/package-configuration-2005/

and

http://www.mssqltips.com/tip.asp?tip=2091

Thanks

Microsoft TechNet Forum Bandara

Share via

Test Connection Failed because of an error initializing provider

Question

All replies (14)

Additional resources