Friday, March 30, 2012

connecting to sql from a remote host

ever install sql on a box and then you can't connect to it from a remote host?  you're trying to install a system center product and you're at the point where it asks where your sql server is and it's time to create some DBs... i've done this plenty of times and depending on whether you're installing an eval version or hosting all roles on a single machine, these settings can vary and can cause a headache.   here's a simple post of a few checks and solutions.

first, see if you can telnet over 1433 from the remote host to the sql server (telnet SQL-IP 1433).  if you can do this, then chances are you aren't reading this :)

what i usually do at this point is RDP directly to the sql server and open the sql server configuration manager.  assuming you're on x64, drill down on the left-side to sql server network configuration and open the protocols for your instance.  this will be mssqlserver if you chose the default instance but could be anything if you chose the named instance.  i'm using sql server 2008 r2 eval and have a named instance of vmm since that happens to be the toy of choice tonight.  ensure that tcp/ip and shared memory are enabled.  open the properties for tcp/ip and click on the ip addresses tab.  this is the important part - ensure the following:

active: yes
enabled: yes
ip address: x.x.x.x
tcp dynamic ports: - must be blank to signify you want it disabled
tcp port: 1433

at the bottom for IPall:

tcp dynamic ports: must be blank to signify you want it disabled
tcp port: 1433

on the protocols tab ensure that enabled and listen all are set to yes, click ok and then restart the sql service.

now, see if you can telnet... betcha can.

No comments:

Post a Comment