VSTO & .NET & Excel

October 2, 2006

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

Filed under: SQL Server — Dennis M Wallentin @ 1:58 pm

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

Advertisements

14 Comments »

  1. Hi Dennis,

    Thanks for this! By the way, how would we use SSPI over a remote connection? I can’t ever really get away with userID & passwords (you know my work) so I would be interested to know how that would work over a Windows network, say… 🙂

    Truthfully I know little to nothing of .NET so this is all excellent knowledge.

    Will

    Comment by Will Riley — October 3, 2006 @ 12:06 am

  2. Hi Will,

    AFAIK it can be applied by using the following parameter in the connection string: Integrated Security=SSPI

    Data Source=SERVERNAME;
    Initial Catalog=DATABASENAME;
    Integrated Security=SSPI

    Is this workable?

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 3, 2006 @ 9:56 am

  3. thanks Dennis:
    this makes sense:
    “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.”

    but is it the CLR that does the marshalling or is there some kinda of IA?

    Comment by Ross — October 3, 2006 @ 10:54 am

  4. Cheers Dennis,

    Will try that out later 🙂 Looks good!

    Comment by Will Riley — October 3, 2006 @ 3:39 pm

  5. Ross,
    It’s not easy to understand everything when it comes to the .NET Framework and CLR. I feel that I have only scratched on the surface of it.
    Here is only the .NET Data Provider, CLR and of course .NET Framework involved. It’s my understanding that the marshaling is taking care of by the CLR and where the Data Provider is the ‘carrier’.
    Later we will see how it works when using the namespace System.Data in both the ‘managed’ COM add-in (VB.NET) and ‘managed’ add-in (VSTO 2005 SEB).
    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 3, 2006 @ 5:46 pm

  6. “It’s not easy to understand everything when it comes to the .NET Framework and CLR. ”

    Amen to that!

    I guess it’s a bit black box, esp inlight of the size of the .Net Framework and the fact that MS are not be make good quality info easily avilable.

    cheers
    Ross

    Comment by Ross — October 4, 2006 @ 10:19 am

  7. You were completely right Dennis,

    With integrated security we get something like this.

    Data Source=PETE;
    Initial Catalog=AdventureWorks;
    Integrated Security=True

    Comment by Will Riley — October 5, 2006 @ 11:00 am

  8. Thanks for the confirmation Will 🙂

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 5, 2006 @ 1:45 pm

  9. Does all this work exactly the same with the express version?

    Also, instead of the
    “Data Source=IBM\SQLLocal”
    could you have used
    “localhost” ??

    Thanx!

    Comment by Anil Gupte — November 10, 2007 @ 7:54 pm

  10. Hi,

    Yes, it’s the same for the expression edition of SQL Server.

    With SQL Server 2000 we can use ‘localhost’ while it’s not possible in 2005.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — November 11, 2007 @ 2:51 pm

  11. Thanks for the tip. Your connection string worked for me and it saved me a lot of time.

    These were the new settings I changed:

    Provider=SQLNCLI.1;
    Integrated Security=SSPI;
    Persist Security Info=False;

    These were what didn’t work for me:

    Provider=SQLNCLI;
    Integrated Security=True;
    (I did not have Persist Security Info)

    Comment by Steve — March 15, 2008 @ 5:35 am

  12. Hi,

    You’re welcome.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — March 15, 2008 @ 2:51 pm

  13. So lets assume we know that SQL Server runs locally, but we don’t know what the name of the currently active instance of SQL Server is.
    Lets also assume that to connect, the user needs to add a username and password to log into SQL Server with.

    How would we connect?

    I ask because I’m currently working on a redistributable guestbook web application and I’m having difficulties. Not so much with facilitating the user log in, but rather connecting to SQL Server.

    Comment by Logan — February 9, 2009 @ 3:46 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: