sql client connect to microsoft sqlserver 2012 express - a must read for non-sql dba

sql client connect to microsoft sqlserver 2012 express - a must read for non-sql dba

long story short,  the problem I got the client working on the server itself, but not from another PC / RDC server

eventually got this working, either due to the firewall or sql browser , I manually opened firewall ports (even if a GPO disabled firewall on domain profile), and enabled the sql browser. suddenly the sql client app starts connected.

here is the link

http://www.mcbsys.com/techblog/2012/12/connect-to-sql-server-2012-express-over-the-network/

I quote / paste here (in case the orginal post disappear)

Connect to SQL Server 2012 Express Over the Network

Mark BerryDecember 18, 2012

I’ve recently installed SQL Server 2012 Express SP1 under Windows Server 2012 Essentials. I’m using it to host GoldMine CRM databases. There is so much information about network and firewall access, I wanted to write down what worked for me.

Important Background

SQL Server Express is by default installed as a named instance. That’s the “SQLEXPRESS” in the SQL server name, e.g. if your server is named SVR2012E, your default SQL Express instance will be named SVR2012E\SQLEXPRESS.

That’s important not only because you need that name to make network connections, but because named instances by default use dynamic TCP ports for their connections, i.e. the port number can change whenever SQL starts. You can reconfigure it to use a fixed port, but I wanted to see if I could get it to use dynamic ports.

SQL Server Setup

This part seems pretty well documented but I’ll repeat it here for completeness.

1. In SQL Management Studio, right-click on the server name and select Properties. Click on Connections, and in the right pane, check Allow remote connections to this server.

2. In SQL Server Configuration Manager, on the left side, highlight the SQL Server Services node. On the right side, highlight the SQL Server Browser service, right-click, and select Properties. On the Service tab, set Start Mode to Automatic. That will start it automatically whenever the server boots. Go ahead and start it manually now (right-click on SQL Server Browser and select Start).

3. Still in SQL Server Configuration Manager, on the left side, click on the SQL Server Network Configuration node, then highlight the Protocols for SQLEXPRESS node. On the right side, right-click on TCP/IP and click Enable. You can also look at theProperties, but they should already be set to allow dynamic connections.

4. After you enable TCP/IP connections, you’ll be advised that you need to restart the SQL Server for the new setting to take effect. You can do that from the SQL Server Services node by right-clicking on SQL Server (SQLEXPRESS) and selectingRestart.

Windows Firewall Setup

This is the area where things seemed a little ambiguous.

1. This TechNet article says that when using dynamic ports, the inbound firewall on the server needs to allow access to the slqservr.exe program, not a port. So for SQL 2012 Express, create an inbound program exception allowing access to

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn\sqlservr.exe

2. What wasn’t clear is that the SQL Server Browser service also needs a firewall exception. Another TechNet article says that the Browser service uses UDPconnections to port 1434. Create an inbound port exception for UDP 1434:

At this point, you should be able to connect remotely to your SQL server.

3. Optional:  for additional security, go back in to each of your firewall rules and set the Scope to restrict access to the Local subnet (assuming all valid connections come from inside your local network):

And yes, the top half of that dialog, Local IP address, is intentionally set to Any IP address. This is referring to the IP addresses of the server. Think of this as the targetIP address for the packets coming in through this rule, whereas the Remote IP addresses are the source IPs. I want this rule to apply no matter what local IP is targeted, but only if the packet comes from a the Local subnet.

Tags: GoldMine, sql

This entry was posted on Tuesday, December 18th, 2012 at 5:54 pm and is filed under IT Administration. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, ortrackback from your own site.