Using SQL Server .NET Data Provider to connect to SQL Server 2005

There was some comments on my previously post about how to connect to SQL Server 2005. I decided to create a special post about it and I hope that it can give some guidelines when it comes to create DSN-less connection strings.

The SQL Server .NET Data Provider uses Tabular Data Stream (TDS) to communicate with the SQL Server, which actually is the same protocol that SQL Server use as the native protocol. The advantage is that by using this Data Provider the performance increase heavily compared with going via the OLE DB layer or the ODBC layer. The CLR (Common Language Runtime) don’t need to marshal the COM data types to .NET CLR data types each time a SQL Server’s database is accessed.

The drawback is that we can’t build connection strings by using the Data Link Wizard in Excel 2000 – 2003 in the same way as we can do for OLE DB Providers and ODBC drivers. It requires that we know all the information before creating the connection strings. 

In Excel 2007 we can use the Data Connection Wizard which also requires that we have all the required information available before building the connection.

Workaround – Creating a connection string in .NET

An alternative approach to retrieve the connection string is to:

• Create a project in VB.NET.
• Start the wizard for connecting to a database, Tools | Connect to database…
• Complete the required information in the main dialog and test the connection.
• Click on the Advanced… button.
• Copy the connection string in the bottom of the dialog.

Syntax – Local connection

In order to connect to a local running instance of SQL Server 2005 the following syntax can be used:

“Provider=SQLNCLI.1;
“Integrated Security=SSPI;
“Persist Security Info=False;
“Initial Catalog=Name of the database;
“Data Source=Name of the Computer\Name of the running instance of SQL Server”

The following connection string is valid for me when connecting to my local server:

“Provider=SQLNCLI.1;
“Integrated Security=SSPI;
“Persist Security Info=False;
“Initial Catalog=AdventureWorks;
“Data Source=IBM\SQLLocal” 

Syntax – Remote connection 

In order to connect to a remote SQL Server 2005 the following syntax can be used:

“Provider=SQLNCLI.1;
“Persist Security Info=False;
“User ID=User;
“Initial Catalog=Name of the database associated with the User ID;
“Data Source=IP-number”

The following connection string is valid for me when connecting to my remote server:

“Provider=SQLNCLI.1;
“Persist Security Info=False;
“User ID=sa;
“Initial Catalog=AdventureWorks;
“Data Source=xx.xxx.xxx.xx”

The above connection strings does not save the password for any UserID which will require some additional processing. Personally I use a ‘Data Access Component’ (DLL) to do the work.

Although I have not tested it but the above connection strings should also work with the Express Edition of SQL Server 2005. 

Kind regards,
Dennis