Enable remote connections for SQL Server Express 2012

Question

I just installed SQL Server Express 2012 on my home server. I'm trying to connect to it from Visual Studio 2012 from my desktop PC, and repeatedly getting the well-known error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

What I've done to try to fix this:

• Run SQL Server Configuration Manager on the server and enable SQL Server Browser
• Add a Windows Firewall exception on the server for TCP, ports 1433 and 1434 on the local subnet.
• Verify that I have a login on the SQL Server instance for the user I'm logged in as on the desktop.
• Verify that I'm using Windows Authentication on the SQL Server instance.
• Repeatedly restart SQL Server and the whole dang server.
• Pull all my hair out.

How can I get SQL Server 2012 Express to allow remote connections!?

Vanitavanity

I solved by enabling the SQL Server Browser service :D. Thanks for posting it. – Neukam

Unlocking 1433 UDP port helped me! – Boltzmann

And if you're using an Azure VM, don't forget to open the port via Azure's management portal as well (which acts as an outer firewall to the VM's own firewall...). Like so: #34251882 – Ahmad

For those wondering where Sql Server Configuration Manager has disappeared to in newer versions, head on over here – Ssr

Answer 1

Well, glad I asked. The solution I finally discovered was here:

How do I configure SQL Server Express to allow remote tcp/ip connections on port 1433?

1. Run SQL Server Configuration Manager.
2. Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.
3. Make sure TCP/IP is enabled.

So far, so good, and entirely expected. But then:

1. Right-click on TCP/IP and select Properties.
2. Verify that, under IP2, the IP Address is set to the computer's IP address on the local subnet.
3. Scroll down to IPAll.
4. Make sure that TCP Dynamic Ports is blank. (Mine was set to some 5-digit port number.)
5. Make sure that TCP Port is set to 1433. (Mine was blank.)

(Also, if you follow these steps, it's not necessary to enable SQL Server Browser, and you only need to allow port 1433, not 1434.)

These extra five steps are something I can't remember ever having had to do in a previous version of SQL Server, Express or otherwise. They appear to have been necessary because I'm using a named instance (myservername\SQLEXPRESS) on the server instead of a default instance. See here:

Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)

Vanitavanity

If SQL Server Express is not the only instance, it might be necessary to UNASSIGN port 1433 on other instances. I had a 2008 R2 default instance (MSSQLSERVER), and the only way I can connect to both of them from outside is to assign port 1433 to the 2012 instance (per above) and change the port assignments for the older default instance to TCP Dynamic Ports = "0" and TCP Port = "" (blank). Doing it the other way around gave access only to the default instance. – Sybarite

That IPAll setting was the SAVIOR advice! Mine was like yours... :D – Bureaucratic

In case it helps anyone else ... this helped, but I still wasn't able to connect until I started the SQL Server Browser service. (Note: I had to go into the Windows "Services" application to do this, because the SQL Server Browser service's startup type was "Disabled" for some reason. Changed the Startup Type to "Automatic", started the service, and was then able to connect.) – Revers

In my case, I don't see the IP2 at all... Where could the config gone wrong? – Cuirass

Very helpful! I just installed SQL 2012 side-by-side with SQL 2008 on a dev server. These steps worked like a charm. I can get to both instances now. – Horatius

@kyralessa Can you think of any additional steps I might try? I've gone through all of those you outlined above but am still getting the same error you were initially getting. (I'm trying to connect from a W7 machine running the 2012 dev version to a VMWare machine running the 2012 Express version.) – Midge

@RobC, nothing comes to mind off the top of my head, but you might benefit from asking a question of your own, explaining that you've done the steps in this one, but it doesn't work, and indicating what error you get. The "network connectivity" error can be caused by a number of things. Are you actually using a user with access to both machines, for instance? – Vanitavanity

@Kyralessa Thanks for getting back to me. I've been attempting to log in using the "sa" account on both machines, so I don't think that would be an issue. I'll post my own question and see if anyone comes up with anything. – Midge

Just a small addition: In my case, after applying these steps I had to restart the "SQL Server" service in order to make it work... – Saccharide

On Windows 8 and SQL 2012 Express SP1 installed to SQLEXPRESS instance I had to set dynamic ports to anything other than blank (if you deleted it, set to 0 then it will re-calculate a new random port for you) AND also open BOTH TCP 1433 and UDP 1434 incoming port rules in the Advanced Firewall control panel. When dynamic ports was blank the SQL Server just hung on start-up. – Catnap

Fought this for a while today. I needed BOTH the TCP port 1433 AND the UDP port 1434 firewall rule. Initially I had only added the TCP rule. Note that this was on Windows Server 2012 running SQL Express 2008 R2. Hope it helps someone. – Thermion

Just wanted to say thanks and upvote. You saved me a lot of time. Do you mind updating your post with the correct connect syntax? I need to use myserver\sqlexpress,[portnumber] without the brackets. Notice it is , and not : – Dickson

In SQL Server 2012 Express, TCP/IP is disabled by default :-( Also, port is not set. – Paragrapher

thanks for your answer. please edit your answer. you can use remote access when SQL Server Browser service started. – Boonie

This is also affecting SQL Server 2014 (Developer Edition). – Gerik

For the full version of SQL Server 2012 on Windows Server, you seem to have to run SQL Server Configuration Manager as administrator to get this to take. – Strahan

maaaaan!!! thank you so much!! that was the issue for me: Make sure that TCP Dynamic Ports is blank. (Mine was set to some 5-digit port number.) Make sure that TCP Port is set to 1433. (Mine was blank.) – Swami

Please add notes to check firewall to allow connection on port 1433. – Fry

If these Steps still don't work: try acessing the server by it's ip-Adress instead of the server name! – Ad

@Kyralessa I have done everything you wrote here, also I added ports/programs to Group Policy and still doesn't work for me. – Eastlake

Thanks for this, I need this every single time I install SQL Server, even Express, even if I select options in the install that seem like they're going to enable TCP/IP connections. – Crete

I found specifying the port manually worked for me when trying to connect. i.e. in SQL Server Management Studio, Connect to Server dialog, specifying the server name as "ASGLH-WL-09515\SQLEXPRESS,1433" – Cuthburt

As an alternative to assigning a static port, you could instead ensure that Windows Firewall (or any other firewalls) allow TCP traffic on all ports for the SQL Server program and UDP traffic on all ports for the SQL Server Browser program. If SQL Server Browser isn't running and accessible, you will have to specify the port when connecting remotely. – Phoney

Thanks, the only answer out there that worked for me. – Mechanism

Answer 2

The correct way to connect to remote SQL Server (without opening UDP port 1434 and enabling SQL Server Browser) is to use ip and port instead of named instance.

Using ip and port instead of named instance is also safer, as it reduces the attack surface area.

Perhaps 2 pictures speak 2000 words...

This method uses the specified port (this is what most people want I believe)..

This method requires opening UDP port 1434 and SQL Server Browser running..

Bursar

Can you elaborate on what you mean by saying that this is the "correct" way to do it? – Vanitavanity

@Kyralessa Ha!... now that I am sober again after 4-5 hours of hair pulling trying to connect to my remote instance, I should probably explain that 'correct' here is meant to be read from my context. It is 'incorrect' to connect using instance name since I didn't enable SQL Server Browser. – Bursar

I have added some clarification to my answer to avoid misunderstanding. – Bursar

Thanks ;) When you don't have access to run the browser service you have to specify the port. – Nosepiece

plus 1 for showing that MS uses comma instead of columns as separator for port number – Frederiksberg

Answer 3

One More Thing...

Kyralessa provides great information but I have one other thing to add where I was stumped even after this article.

Under SQL Server Network Configuration > Protocols for Server > TCP/IP Enabled. Right Click TCP/IP and choose properties. Under the IP Addresses you need to set Enabled to Yes for each connection type that you are using.

Shotputter

Answer 4

You can use this to solve this issue:

Go to START > EXECUTE, and run CLICONFG.EXE.

The Named Pipes protocol will be first in the list.Demote it, and promote TCP/IP.

Test the application thoroughly.

I hope this help.

Potamic

Wow, what a gem... thanks. Never knew that even existed. – Middlebrooks

This configures the CLIENT to use TCP/IP, not the server. – Duer

OMG, It works!! just signed in to vote for this answer. thanks – Church

Answer 5

You can also set

Listen All to NO

in the protocol dialog then in the IP address IP1 (say)

set enabled to Yes,

define yr IP address,

set TCP Dynamic to Blank and

TCP port to 1433 (or whatever)

Gordon

Doing this on Windows 8.1 with SQL 2012 Express SP1 installed caused SQL to hang on start up :( – Klipspringer

I set all of the IP entries 1, 2, ..., to enabled + active, erased dynamic, erased port, and set IPAll port to 1433, then had to add rules to open TCP 1433 and UPD 1434 in the firewall (1434 is not required if not using name, but only the port number). – Rittenhouse

Answer 6

I had this problem recently. 2015 Aug

Solved by opening SQL Server Configuration Manager

• SQL Server Network Configuration -> Protocols for SQLEXPRESS
• Properties on TCP/IP -> IP Adresses tab
• Everything stays default, only set IPALL: TCP Port to 1433

Can connect to with SQL Server Manager to machine: [hostaddress], 1433

Example:

Seema

Answer 7

This article helped me...

How to enable remote connections in SQL Server

Everything in SQL Server was configured, my issue was the firewall was blocking port 1433

Supreme

Everyone knows, or easily comes across the Azure allowed ports config. Didn't cross my mind the port may be blocked by the WINDOWS VM firewall. Thanks.+1 – Gadid

Answer 8

On my installation of SQL Server 2012 Developer Edition, installed with default settings, I just had to load the SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for MSSQLSERVER and change TCP/IP from Disabled to Enabled.

Simeon

Quick note: for me, this didn't work. The wrong IP was in there for some reason. However, Kyralessa's steps did the trick since they invovled updating the IP. – Horatius

This works for one of my server, but not for the other. – Equivocation

Answer 9

I had to add a firewall inbound port rule to open UDP port 1434. This is the one Sql Server Browser listens on.

Unlicensed

Answer 10

I prefer way of "Rosdi Kasim" as that's doesn't require detail configuration on the IP.

I will definitely forget it again when I try to up another server again.

Keep It Simple Stupid (KISS) by simply enable the Sql Server Browser service, then add the \SQLEXPRESS behind the IP when you connect the server.

Direct using IP without "\SQLEXPRESS" was my point of failure as it doesn't use the default port.

Thanks.

Crocked

Answer 11

I had to add port via Configuration Manager and add the port number in my sql connection [host]\[db instance name],1433

Note the , (comma) between instancename and port

Herold

Answer 12

I had the same issue with SQL Server 2014 locally installed named instance. Connecting using the FQDN\InstanceName would fail, while connecting using only my hostname\InstanceName worked. For example: connecting using mycomputername\sql2014 worked, but using mycomputername.mydomain.org\sql2014 did not. DNS resolved correctly, TCP/IP was enabled within SQL Configuration Manager, Windows Firewall rules added (and then turned the firewall off for testing to ensure it wasn't blocking anything), but none of those fixed the problem.

Finally, I had to start the "SQL Server Browser" service on the SQL Server and that fixed the connectivity issue.

I had never realized that the SQL Server Browser service actually assisted the SQL Server in making connections; I was under the impression that it simply helped populate the dropdowns when you clicked "browse for more" servers to connect to, but it actually helps align client requests with the correct port # to use, if the port # is not explicitly assigned (similar to how website bindings help alleviate the same issue on an IIS web server that hosts multiple websites).

This connect item is what gave me the clue about the SQL Server Browser service: https://connect.microsoft.com/SQLServer/feedback/details/589901/unable-to-connect-on-localhost-using-fqdn-machine-name

• when you use wstst05\sqlexpress as a server name, the client code separates the machine name from the instance name and the wstst05 is compared against the netbios name. I see no problem for them to match and the connection is considered local. From there, we retrieve the needed information WITHOUT contacting SQL Browser and connect to the SQL instance via Shared Memory without any problem.
• when you use wstst05.capatest.local\sqlexpress, the client code fails the comparison of the name (wstst05.capatest.local) to the netbios name (wstst05) and considers the connection "remote". This is by design and we will definitely consider improving this in the future. Anyway, due to considering the connection remote and the fact that it is a named instance, client decides that it needs to use SQLBrowser for name resolution. It attempts to contact SQL Browser on wstst05.capatest.local (UDP port 1434) and apparently that part fails. Hence the error you get.

The reason for the "SQL Server Browser" service from TechNet (emphasis added by me): https://technet.microsoft.com/en-us/library/ms181087(v=sql.120).aspx

From the "Using SQL Server Browser" section:

If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433. However, if the SQL Server Browser service is not running, the following connections do not work:

• Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).
• Any component that generates or passes server\instance information that could later be used by other components to reconnect.
• Connecting to a named instance without providing the port number or pipe.
• DAC to a named instance or the default instance if not using TCP/IP port 1433.
• The OLAP redirector service.
• Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.

If you are using SQL Server in a client-server scenario (for example, when your application is accessing SQL Server across a network), if you stop or disable the SQL Server Browser service, you must assign a specific port number to each instance and write your client application code to always use that port number. This approach has the following problems:

• You must update and maintain client application code to ensure it is connecting to the proper port.
• The port you choose for each instance may be used by another service or application on the server, causing the instance of SQL Server to be unavailable.

And more info from the same article from the "How SQL Server Browser Works" section:

Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express. By default, when enabled, both named instances and SQL Server Express are configured to use dynamic ports, that is, an available port is assigned when SQL Server starts. If you want, a specific port can be assigned to an instance of SQL Server. When connecting, clients can specify a specific port; but if the port is dynamically assigned, the port number can change anytime SQL Server is restarted, so the correct port number is unknown to the client. ... When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance

Bipetalous

Answer 13

I had a different problem from what all of the answers so far mentioned!

I should start off by saying that I had it in Visual Studio, and not SQL Server Express but the solution should be exactly the same.

Well, god, it's actually really simple and maybe a bit foolish. When I tried to create a database and Visual Studio suggested the name of the SQL Server it gave me my Windows username and since it's actually the name of the server I went for it.

In reality it actually was my Windows username + \SQLEXPRESS. If you didn't change any settings this is probably yours too. If it works, stop reading; this is my answer. If it doesn't work maybe the name is different.

If, like me, you only had this problem in Visual Studio to check what is yours follow these steps:

1. Open SQL Server Management Studio^icon.
2. If you don't see your server (docked to the left by default) press F8 or go to View -> Object Explorer.
3. Right click on the name of the server and choose Properties (The last item)
4. At the bottom left you can see your server's actual name under "Server" (not Connection, but above it).

This is the name of the server and this is what you should attempt to connect to! not what Visual Studio suggests!

Zerline

Answer 14

One more thing to check is that you have spelled the named instance correctly!

This article is very helpful in troubleshooting connection problems: How to Troubleshoot Connecting to the SQL Server Database Engine

Ninety

Answer 15

In my case the database was running on non standard port. Check that the port you are connecting is the same as the port the database is running on. If there are more instances of SQL server, check the correct one.

Fray

Answer 16

All you need to do is open the relevant port on the server's firewall.

Sprinkle

Unfortunately, that is not "all you need to do". There are quite a few other steps that need to be taken, as outlined in the accepted answer above. – Lissner

Actually, in my case this WAS all I needed to do, so unfairly marked down and actually useful input. – Wayworn

Adding a rule to open port 1433 was what fixed it for me. – Disassemble

I think it's fair to say "a lot of things have to be in alignment, what additional steps are necessary for you will vary" – Rittenhouse

I had to do all of Kyralessa's, and Pete's and this one as well before I could get Windows 10 serving SQL. – Glarus

Answer 17

Having problems connecting to SQL Server?

Try disconnecting firewall.

If you can connect with firewall disconnected, may be you miss some input rules like "sql service broker", add this input rules to your firewall:

"SQL ADMIN CONNECTION" TCP PORT 1434

"SQL ADMIN CONNECTION" UDP PORT 1434

"SQL ANALYSIS SERVICE" TCP PORT 2383

"SQL BROWSE ANALYSIS SERVICE" TCP PORT 2382

"SQL DEBUGGER/RPC" TCP PORT 135

"SQL SERVER" TCP PORT 1433 and others if you have dinamic ports

"SQL SERVICE BROKER" TCP PORT 4022

Convincing