How to form the connection string for a SQL Server express instance?

Question

I don't know how to form the connection string.

Lets say the server is server.com, with a SQL Instance called MSSQL.1

Looking at a previous example, it looks like the data source would be server.com\MSSQL.1

I installed SQL Express using all the defaults on Windows Server 2003.

Any help is appreciated. Thanks

Kevin

Answer 1

Try this site:

http://connectionstrings.com/sql-server-2008

Answer 2

Data Source=server.com\MSSQL.1;Initial Catalog=DataBaseName;User ID=username; Password=password

If you use windows authentication use this connection string:

Data Source=server.com\MSSQL.1;Initial Catalog=DataBaseName;Integrated Security=True

Answer 3

Just a hint: if you're using an IDE (e.g. Visual Studio) you can use a Connection component and connect to your instance via a GUI interface, then examine the properties of the Connection.

Answer 4

MSSQL.1 is most likely not the instance name, but the files location in \program files\microsoft sql server\.... The installer uses this convention for placing each instance data, but the name of the instance is different. The instance name is usualy SQLEXPRESS for an Express instalation with 'all the defaults' so its connection string would be Data Source=server.com\SQLEXPRESS;.... Also a default instalation with 'all the defaults' will not be possible to connect remotely as it would not allow remote connections. You must enable remote connections from the Surface Area Configuration: How to configure SQL Server 2005 to allow remote connections. You'll also need to enable the SQL Browser service so that the clients are able to discover the listennig port of an non-default instance. And you need to poke holes in the firewall to allow incomming packets to the SQL Broswer (UDP 1433) and the Express listenning port (TCP, port number it depends as is usually dynamic for named instances).

Answer 5

guys,

SQL Server 2005 Express Edition always installs as a named instance, even if you only have one. The instance name, by default, is SQLEXPRESS. MSSQL.1 is NOT an instance name.

You connection string should look similar to this one:

<connectionStrings> 
<add name="SQLServer" connectionString="Server=.\SQLExpress;Database=MyDBName;Uid=MyUserName;Pwd=MyPassword;"/> 
</connectionStrings>