MSSQL 2000 connection strings

[Rock]

Hi,

I find a lot of people not very much comfortable with connection strings. It's a string passed in code that specifies connection information directly to the Driver Manager and driver.

SQL Server 2000

This is a compiled connection strings reference list on how to connect to SQL Server.
ODBC Standard Security

Driver={SQL Server};Server=myServerAddress;Database=myDataBase ;Uid=myUsername;Pwd=myPassword;

Trusted connection

Driver={SQL Server};Server=myServerAddress;Database=myDataBase ;Trusted_Connection=Yes;

Prompt for username and password
This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways

Driver={SQL Server};Server=myServerAddress;Database=myDataBase ;

OLE DB, OleDbConnection (.NET)
Standard Security

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Trusted connection

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Use serverName\instanceName as Data Source to use a specific SQL Server instance. Please note that the multiple SQL Server instances feature is available only from SQL Server version 2000 and not in any previous versions.

Prompt for username and password
This one is a bit tricky. First set the connection object's Provider property to "sqloledb". Thereafter set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways

Data Source=myServerAddress;Initial Catalog=myDataBase;

Connect via an IP address

Provider=sqloledb;Data Source=192.168.0.1,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

DBMSSOCN=TCP/IP. This is how to use TCP/IP instead of Named Pipes. At the end of the Data Source is the port to use. 1433 is the default port for SQL Server.

SqlConnection (.NET)
Standard Security

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Standard Security alternative syntax
This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connecti on=False;

Trusted Connection

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Trusted Connection alternative syntax
This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.

Server=myServerAddress;Database=myDataBase;Trusted _Connection=True;

Use serverName\instanceName as Data Source to use a specific SQL Server instance. Please note that the multiple SQL Server instances feature is available only from SQL Server version 2000 and not in any previous versions.
Connect via an IP address

Data Source=192.168.0.1,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

DBMSSOCN=TCP/IP. This is how to use TCP/IP instead of Named Pipes. At the end of the Data Source is the port to use. 1433 is the default port for SQL Server.

Specifying packet size

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connecti on=False;Packet Size=4096;

By default, the Microsoft .NET Framework Data Provider for SQL Server sets the network packet size to 8192 bytes. This might however not be optimal, try to set this value to 4096 instead.

Data ShapeMS Data Shape

Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=myServerAddress;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

I'll be posting an article regarding connecting to an MSSQL 2005 database shortly.

[DPS Computing]

Thanks for that Rock - I'm sure people will find it useful - its been really good to see the amount of tutorials being posted recently!

[Rock]

As promised, here's an article for connecting to MSSQL 2005 database from your scripts/web-pages : http://www.eukhost.com/forums/showthread.php?p=16110

[DPS Computing]

The new tutorial on MSSQL 2005 database connection strings is definitely comprehensive!! Maybe some of these should start getting added to the support section of the website hosting or the knowledgebase - they are really good and helpful.