Asked  7 Months ago    Answers:  5   Viewed   57 times

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!?

 Answers

25

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 (myservernameSQLEXPRESS) on the server instead of a default instance. See here:

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

Tuesday, June 1, 2021
 
Neysor
answered 7 Months ago
67

I tested below code with SQL Server 2008 R2 Express and I believe we should have solution for all 6 steps you outlined. Let's take on them one-by-one:

1 - Enable TCP/IP

We can enable TCP/IP protocol with WMI:

set wmiComputer = GetObject( _
    "winmgmts:" _
    & "\.rootMicrosoftSqlServerComputerManagement10")
set tcpProtocols = wmiComputer.ExecQuery( _
    "select * from ServerNetworkProtocol " _
    & "where InstanceName = 'SQLEXPRESS' and ProtocolName = 'Tcp'")

if tcpProtocols.Count = 1 then
    ' set tcpProtocol = tcpProtocols(0)
    ' I wish this worked, but unfortunately 
    ' there's no int-indexed Item property in this type

    ' Doing this instead
    for each tcpProtocol in tcpProtocols
        dim setEnableResult
            setEnableResult = tcpProtocol.SetEnable()
            if setEnableResult <> 0 then 
                Wscript.Echo "Failed!"
            end if
    next
end if

2 - Open the right ports in the firewall

I believe your solution will work, just make sure you specify the right port. I suggest we pick a different port than 1433 and make it a static port SQL Server Express will be listening on. I will be using 3456 in this post, but please pick a different number in the real implementation (I feel that we will see a lot of applications using 3456 soon :-)

3 - Modify TCP/IP properties enable a IP address

We can use WMI again. Since we are using static port 3456, we just need to update two properties in IPAll section: disable dynamic ports and set the listening port to 3456:

set wmiComputer = GetObject( _
    "winmgmts:" _
    & "\.rootMicrosoftSqlServerComputerManagement10")
set tcpProperties = wmiComputer.ExecQuery( _
    "select * from ServerNetworkProtocolProperty " _
    & "where InstanceName='SQLEXPRESS' and " _
    & "ProtocolName='Tcp' and IPAddressName='IPAll'")

for each tcpProperty in tcpProperties
    dim setValueResult, requestedValue

    if tcpProperty.PropertyName = "TcpPort" then
        requestedValue = "3456"
    elseif tcpProperty.PropertyName ="TcpDynamicPorts" then
        requestedValue = ""
    end if

    setValueResult = tcpProperty.SetStringValue(requestedValue)
    if setValueResult = 0 then 
        Wscript.Echo "" & tcpProperty.PropertyName & " set."
    else
        Wscript.Echo "" & tcpProperty.PropertyName & " failed!"
    end if
next

Note that I didn't have to enable any of the individual addresses to make it work, but if it is required in your case, you should be able to extend this script easily to do so.

Just a reminder that when working with WMI, WBEMTest.exe is your best friend!

4 - Enable mixed mode authentication in sql server

I wish we could use WMI again, but unfortunately this setting is not exposed through WMI. There are two other options:

  1. Use LoginMode property of Microsoft.SqlServer.Management.Smo.Server class, as described here.

  2. Use LoginMode value in SQL Server registry, as described in this post. Note that by default the SQL Server Express instance is named SQLEXPRESS, so for my SQL Server 2008 R2 Express instance the right registry key was HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLServer.

5 - Change user (sa) default password

You got this one covered.

6 - Finally (connect to the instance)

Since we are using a static port assigned to our SQL Server Express instance, there's no need to use instance name in the server address anymore.

SQLCMD -U sa -P newPassword -S 192.168.0.120,3456

Please let me know if this works for you (fingers crossed!).

Thursday, June 3, 2021
 
coolguy
answered 6 Months ago
77

Thanks everyone, I used a combination of your responses.

In my app.config file my connection string is defined as follows

<add name="MyConnectionString"
    connectionString="Server=.SQLExpress;AttachDbFilename=|DataDirectory|MyDatabase.mdf;Database=MyDatabaseForTesting;Trusted_Connection=Yes;" />

In my unit test class I set the DataDirectory property using the following

[TestInitialize]
public void TestInitialize()
{
    AppDomain.CurrentDomain.SetData("DataDirectory", System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Databases"));

    // rest of initialize implementation ...
}
Monday, June 7, 2021
 
Octopus
answered 6 Months ago
21

downloaded sql server express 2008 (SQLEXPR32_x86_ENU.exe) and place it in the root of my c drive. then I install it with the following parameters:

C:SQLEXPR32_x86_ENU.exe /q /hideconsole /action=Install /features=SQL /instancename=SQLEXPRESS /enableranu=1 /sqlsvcaccount="NT AuthorityNetwork Service" /AddCurrentUserAsSqlAdmin /skiprules=RebootRequiredCheck /TCPENABLED=1

I add /TCPENABLED=1 in order to enable TCP/IP

Saturday, August 14, 2021
 
Niels
answered 4 Months ago
78

With that error message in your comment you should run through the items in http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

I presume the instance is running and does allow connections over tcpip?

Sunday, October 17, 2021
 
jezrael
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share