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?
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.
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.
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
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"
