8

On my developer computer I have an SQL Server instance named developer_2005. In the resource setting files of a C# application we are creating, the instance name is set to foobar (not really, but just as an example). So when I run the application (in debug or realease) it tries to connect to an SQL Server on localhost, named foobar.

I am wondering if it is possible to create an alias or something like that, so that the application actually finds an SQL Server on localhost named foobar, but it is actually connecting to the instance named developer_2005.

The connection string in the config file of the application is Data Source=localhost\foobar;Initial Catalog=barfoo;Integrated Security=True with provider name System.Data.SqlClient. If I change localhost\foobar to localhost\developer_2005 then the application can connect like it should. How can I create an alias so that I won't have to change the string in the file?

I tried, in SQL Server Management Studio, to create a Server Registration with registered server name "localhost\developer", but this didn't seem to do any good. Not even sure what that really did... But then I discovered SQL Server Configuration Manager\SQL Native Client COnfiguration\Aliases. And I kind of assume this is where the solution lies. But I can't quite figure out how to add a new one... When creating a new one, I have to provide Alias Name, Port No, Protocol and Server, and I don't really have a clue what to put in either of them.

Svish
  • 6,627
  • 14
  • 37
  • 45

2 Answers2

6

Alias name: foobar (the name you would like to use)

Server name: the actual name of your computer or the IP address (not localhost or 127.0.0.1)

Protocol: TCP/IP

Port: the default one (1433)

Make sure TCP/IP protocoll is enabled (disabled by default)

Change your connection string to use your new alias (Data Source=foobar)

Hakan Winther
  • 481
  • 2
  • 5
  • The server name, *localhost*, is correct. It is the named instance name I need to create an alias for. – Svish Aug 31 '09 at 12:18
  • Then you should use *localhost\instancename* as the server name. – Massimo Aug 31 '09 at 12:20
  • what about the alias name? Should I use *localhost\developer_2005* as server name and *localhost\foobar* as alias? Will that work? – Svish Aug 31 '09 at 12:21
  • I checked better... looks like you actually *can* do that. At least in SQL server 2008 (don't know if it will work in 2005). – Massimo Aug 31 '09 at 12:26
  • I edited my answer, try that. – Massimo Aug 31 '09 at 12:28
  • Interesting. Will try that. – Svish Aug 31 '09 at 12:43
  • Totally worked :D – Svish Aug 31 '09 at 12:56
  • Because this answer came with the "change your connection string to use your new alias" step, which solved it for me. An alias name of *localhost\foobar* didn't work here. Probably because of SQL Server 2005. Upvoted you both though. If you add info about this in your answer I could move the Accepted answer maybe. Cause yours is cleaner set up more informational. – Svish Aug 31 '09 at 13:32
  • That's ok, I thought it actually worked *without* changing the connection string, but that's not your case. – Massimo Aug 31 '09 at 13:50
1

You are right, what you need to do is defining a new alias in the Native Client configuration.

  • Alias name: the alias you want to create
  • Server name: the actual server name
  • Protocol: TCP/IP
  • Port: the default one (1433)

Try this:

  • Alias name: localhost\foobar
  • Server name: localhost\developer_2005
  • Protocol: TCP/IP
  • Port: 1433
Massimo
  • 68,714
  • 56
  • 196
  • 319
  • And that is where I am. But what do I put in those fields? I don't quite get it... – Svish Aug 31 '09 at 12:07
  • The connection string in the config file is *Data Source=localhost\foobar;Initial Catalog=barfoo;Integrated Security=True* with provider name *System.Data.SqlClient*. For it to connect correctly I must change *localhost\foobar* to *localhost\developer_2005*. How can I create an alias so that I don't have to do that? – Svish Aug 31 '09 at 12:10
  • This can't be done; your connection string is looking for a **named instance** called *foobar*, aliases can only be used as top-level names, not as they were named instances themselves. – Massimo Aug 31 '09 at 12:13
  • So there is no way of solving this, other than changing the connection string in the application? – Svish Aug 31 '09 at 12:23
  • I checked better... looks like you actually can do that. At least in SQL server 2008 (don't know if it will work in 2005). – Massimo Aug 31 '09 at 12:29
  • Aha. Maybe that is why it doesn't work here then. – Svish Aug 31 '09 at 12:39
  • If your alias is pointing to a named instance, you need to take into account TCP ports; have a look here: http://support.microsoft.com/kb/265808. – Massimo Aug 31 '09 at 12:54
  • Add this in case anyone else comes along and find the solution here doesn't work. Turns out I had to clear the Port setting from the alias to get this to work for me. – Michael Itzoe May 13 '10 at 14:17
  • me too. and I would also add that my options included a node for 32-bit as well as 64 bit. It was the 32-bit that I had to configure for SQL 2008 R2 Express – Mike L Apr 15 '11 at 18:18