OLEDB connection to MSSQL Server

[Galaxiom]

I am having trouble connecting to MS SQL Server 2005 using an OLEDB DSN-less connection string. Server uses Windows Authentication.

This returns an error Login Failed for user 'username':

"Provider=SQLOLEDB;Data Source=servername;Initial Catalog=databasename;Trusted_Connection=True"

Should there be another argument?

The ODBC connection works:

"Driver={SQL Server}; Server=server; Database=databasename"

 

[boblarson]

At http://www.connectionstrings.com it shows:

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

 

[Galaxiom]

Thanks Bob. I had looked at many sites which showed quite a lot of variation.

Got it working.
Database=servername or Initial Calalog=servername both work.

Provider arguments including both the native client (SQLNCLI) and the SQLOLEDB worked. I understand Native Client is for use with SQL Server 2005 and Native Client10 (SQLNCLI10) with SQL Server 2008.

In the end the problem was Trusted_Connection=True.
The correct argument is Trusted_Connection=Yes

True and Yes are interchangeable in many places but apparently not here. From the connectionstrings site it would seem the True works in .NET

The answer came to light with Provider=SQLNCLI which errored with:
Invalid value specified for connection string attribute 'Trusted_Connection'

Provider=SQLOLEDB does not provide the detail of the error.

It makes sense to use the Native Client where the Server version is known as this is the most uptodate and clearly has better error messages.

 

[LPurvis]

If you're building ADO connections then there's a class here which might help with a point and shoot methodology.
(Bear in mind it's a bit out of date, but any bugs do feel free to mention anyway - but it's not worth posting again, for all its size).

Cheers.