3

I am not associated with database maintenance task. I am application developer. I installed SQL Server 2008 Express edition on my computer as an instance localhost\sqlexpress .

How do I use only the IP address to connect to this server rather than ip\sqlexpress ?

If multiple instances are possible and I am connecting with IP then how can I choose any instance of server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
kinkajou
  • 3,664
  • 25
  • 75
  • 128
  • just curious, why would you want to install multiple SQL server instances on the same machine? You can run "unlimited" SQL-databases within a single instance. – BerggreenDK Jan 04 '13 at 22:08
  • @BerggreenDK folks are using old database instance. I couldn't just swap 2005 database to 2008 on same CPU without some twik. – kinkajou Jan 06 '13 at 12:57
  • @Kitix - I have a server running both SQL 2000 and SQL 2008 Express along side. I connected with them through port numbers + TCPIP. No instance name needed. – BerggreenDK Jan 07 '13 at 16:36
  • @Kitix Nope, the port is the difference and why I dont need instances. I have tested it more times since last I commented. If I connect through DNS/IP I use different custom ports to each instance. This can be configured in SQL Server network settings. I believe if you want to connect to both on same port, you will still need instance names. – BerggreenDK Jan 17 '13 at 08:14
  • @BerggreenDK Hmm. I got you. That's also cool solution by altering ports. Well thanks. :) – kinkajou Jan 17 '13 at 09:01
  • No problem. Btw. placing SQL servers on "none default ports" also removes SOME of the Denial of Service attacks from common SQL-jammer robots, as they would have to scan for 65536 possible SQL ports instead of default port 1433. :-) + I usually lock the custom ports to certain external IP's only, so there is no response unless they are REALLY good at guessing. – BerggreenDK Jan 19 '13 at 23:34

4 Answers4

2

You can use an IP address to reference the instance. It would be something like xxx.xxx.xxx.xxx\sqlexpress.

If you were to just use the IP addess (or host name) you would have to have a default instance installed on the machine. If you have no default instance installed then you will not connect, and you'll return an error.

SQL Server Express is a named instance by default and will require the named instance datasource reference (ip/hostname followed by instance name). You will need to install Express as a default instance to reference by ip only.

In other words, no you cannot connect to a SQL Server Express instance with IP only unless you have installed Express as the default instance.

  • 2
    No, SQL Server Express will certainly allow you to install a default instance. – djangofan Oct 18 '11 at 00:41
  • @djangofan you are absolutely right. Thanks for the correction, I have edited to reflect the correction. Thank you. –  Oct 18 '11 at 00:44
  • No problem... i had to take the opportunity to try to get the points since I saw that one was wide open. – djangofan Oct 18 '11 at 00:46
  • I've just managed to connect to SQL server Express 2008 without Instance name, over DNS/TCPIP address and a portnumber. – BerggreenDK Jan 04 '13 at 22:06
2

The reason why you need to add instance name in your server name is that this will allow SQL Server Browser Service to help you find the corresponding TCP/IP port based on your instance name. For default instance (MSSQLSERVER), it is enough to use IP address or host name only.

If you want to use IP address only, you still need to add the TCP/IP port as well, such as 192.168.0.1, 1434. If not TCP/IP port allowed, the client will try to use TCP/IP 1433 port instead. In this case, if the instance is not listening on 1433 port, the connection will fail.

Hope this helps.

Alex Feng
  • 41
  • 1
  • 3
  • Totally agree, just did this tonight with two different domains/TCPIP addresses and ports. The EXPRESS instance name was different, but I forgot to change it when I switched server address and they both worked. So I tried to remove instance name - worked perfectly. – BerggreenDK Jan 04 '13 at 22:07
1

Yes, when you install SQL Server Express, just choose the "install as default instance" option during the installation. When your done, enable port 1433, open your windows firewall, enable the user account you want to use, and your good to go.

Then, you can connect to 127.1 on port 1433 . The JDBC url would be as simple as:

jdbc:sqlserver://localhost

or

jdbc:sqlserver://127.0.0.1
djangofan
  • 28,471
  • 61
  • 196
  • 289
0

If you want to find the port number your instance is running on (works with Std Sql server as well):

Connect to your instance using Management studio:

Run:

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT
Select @tcp_port;

This will return the port number:

So your server for any connections will be your IP Address which you can get with ipconfig and the port.

For a connection string using most .NET managed providers for SQL Server in applications, you could use something like:

"Server=xxx.xxx.xxx.xxxx,portNumber;Database=dbName;User Id=user;Password=jklhkljh"

Ta01
  • 31,040
  • 13
  • 70
  • 99