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

User · Answer

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)..

enter image description here

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

enter image description here

User · Answer

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    Run SQL Server Configuration Manager  Go to SQL Server Network Configuration   Protocols for SQLEXPRESS  Make sure TCP IP is enabled    So far  so good  and entirely expected   But then    Right-click on TCP IP and select Properties  Verify that  under IP2  the IP Address is set to the computer s IP address on the local subnet  Scroll down to IPAll  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      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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    Open SQL Server Management Studioicon  If you don t see your server  docked to the left by default  press F8 or go to View -  Object Explorer  Right click on the name of the server and choose Properties  The last item  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

User · Answer

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

User · Answer

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

User · Answer

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  quot SQL Server Browser quot  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  quot browse for more quot  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  quot remote quot   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  quot SQL Server Browser quot  service from TechNet  emphasis added by me   https   technet microsoft com en-us library ms181087 v sql 120  aspx From the  quot Using SQL Server Browser quot  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  quot How SQL Server Browser Works quot  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server-2012] Enable remote connections for SQL Server Express 2012

Examples related to sql-server-2012

Examples related to remote-connection

Examples related to sql-server-2012-express