7

Is it possible to connect to a SQL Server Express 2008 database in ASP.NET with a server name of (local) like "server=(local);integrated security=SSPI;database=DBNAME"?

I'm working with another developer on a project and it's getting annoying having to have 2 different version of web.config because he is using SQL Server 2008 and (local) works, but I can't seem to get it to work with SQL Server 2008 Express locally.

The database is located on the same computer as the .NET code in case that matters.

Thanks.

Jeff Treuting
  • 13,910
  • 8
  • 36
  • 47
  • Are you sure your SQL Server Express installation isn't a named instance? – PatrickJ May 18 '10 at 00:14
  • No I'm not sure. How would I check that? – Jeff Treuting May 18 '10 at 00:15
  • SQL Server Express installations are *always* named. – Dean Harding May 18 '10 at 00:17
  • Is the instance name `MSSQLSERVER` (default) OR `SQLEXPRESS` (named)? You can check in the SQL Server Configuration Manager. – John Rasch May 18 '10 at 00:17
  • It is SQLEXPRESS. I can connect to it fine by using a server name of MY-PC\SQLEXPRESS. I would just like to be able to use (local) for ease of development between me and another developer. – Jeff Treuting May 18 '10 at 00:20
  • 1
    I'm not sure that's true codeka - I think Express is named by default, but you can choose to use an unnamed instance during installation. – PatrickJ May 18 '10 at 00:21
  • Also I *think* that "(local)" forces the connection to use named pipes, whereas "." or "localhost" will use TCP. Might need to check which is configured on the server. – Matt Hamilton May 18 '10 at 00:22
  • PatrickJ: I think you may be right, actually... you can install it as a default instance. But in this case, it seems like that's not the case. I think my answer is applicable in that case. – Dean Harding May 18 '10 at 00:24

2 Answers2

4

Assuming you both have SQL Express installed with the default instance name "SQLEXPRESS", you can have 1 connection string like:

server=.\SQLEXPRESS;integrated security=SSPI;database=DBNAME

The "." in the connection string is used to represent the local machine.

Wallace Breza
  • 4,898
  • 1
  • 28
  • 33
1

You can use the SQL Server Configuration Manager tool to create an alias. Give the alias the same name on both your machine, and then you can just reference the alias in your configuration files.

I don't know if the Configuration Manager tool is actually included with the Express version of SQL Server, but if not then you can just use the registry key (it still works with Express editions). Just get your coworker to set up the alias and then export the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

You can then import that key on your local computer. Things are slightly more complicated if you're on 64-bit Windows (because you have to set up the alias in both the 64-bit and 32-bit registry).

Dean Harding
  • 71,468
  • 13
  • 145
  • 180
  • I am on a 64-bit machine. In SQL Server Configuration Manager I setup an Alias under both the 32-bit and 64-bit SQL Native Client 10.0 Configuration sections. Then using that alias as the server name in the connection string I get the following error: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)" – Jeff Treuting May 18 '10 at 00:38
  • So one more bit of info that I think is helping to cause me problems is that I have a full version of 2005 installed on the same machine. When I go to SSMS and connect to (local) it is taking me to the 2005 instance. I also just saw that when I go to the SQL Server Configuration Manager for my 2005 instance it also now has the Alias listed. I'm thinking that uninstalling 2005 might just solve my problems. – Jeff Treuting May 18 '10 at 00:50
  • Another tip about setting up the alias...if it's localhost, you can use "Named Pipes" for the protocol. That worked for me, whereas I couldn't get the TCP protocol to work, even after enabling the protocol. – Dan Tanner Jun 10 '10 at 04:13