asked on

SQL Server needs to be configured to allow remote connections

I am trying to setup SQL 2016 on a test computer. I installed the server and management studio on the same box. I am having issues accessing the server. I disabled windows firewall and I still get this message. I also checked on the database and it seems to be defaulted to port 1433. What troubleshooting can I try? I know it says that SQL server needs to be configured to allow remote connections but I can't even access the DB to make that change!

Shaun Vermaak

Do not disable Firewall, run this. It will open all the required ports, even for instances

$SqlKey = Get-ChildItem -ErrorAction SilentlyContinue "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";
If ($SqlKey -ne $null)
{
    netsh advfirewall firewall delete rule name="MSSQL - SQL Server"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Admin Connection"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Database Management"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Service Broker"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Debugger/RPC"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Server Browse Button Service"
    netsh advfirewall firewall delete rule name="MSAS - SQL Analysis Services"
    netsh advfirewall firewall delete rule name="MSAS - SQL Browser"
    netsh advfirewall firewall delete rule name="MSRS - HTTP"
    netsh advfirewall firewall delete rule name="MSRS - SSL"

    netsh advfirewall firewall add rule name="MSSQL - SQL Server" dir=in action=allow protocol=TCP localport=1433
    netsh advfirewall firewall add rule name="MSSQL - SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434
    netsh advfirewall firewall add rule name="MSSQL - SQL Database Management" dir=in action=allow protocol=UDP localport=1434
    netsh advfirewall firewall add rule name="MSSQL - SQL Service Broker" dir=in action=allow protocol=TCP localport=4022
    netsh advfirewall firewall add rule name="MSSQL - SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135
    netsh advfirewall firewall add rule name="MSSQL - SQL Server Browse Button Service" dir=in action=allow protocol=UDP localport=1433
    netsh advfirewall firewall add rule name="MSAS - SQL Analysis Services" dir=in action=allow protocol=TCP localport=2383
    netsh advfirewall firewall add rule name="MSAS - SQL Browser" dir=in action=allow protocol=TCP localport=2382
    netsh advfirewall firewall add rule name="MSRS - HTTP" dir=in action=allow protocol=TCP localport=80
    netsh advfirewall firewall add rule name="MSRS - SSL" dir=in action=allow protocol=TCP localport=443

    $SubKeys = Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";
    ForEach ($SubKey in $SubKeys)
    {
        If ($SubKey.Name.Contains("\MSSQL") -Anc !$SubKey.Name.Contains("\MSSQLServer"))
        {
           $InstanceName = $SubKey.Name.Split("\")[4]
           $RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
           $EXEPath = "$($RegistryKey.SQLBinRoot)\sqlservr.exe";

           netsh advfirewall firewall delete rule name="MSSQL - $($InstanceName)"
           netsh advfirewall firewall add rule name="MSSQL - $($InstanceName)" dir=in action=allow program="$($EXEPath)"
        }
        If ($SubKey.Name.Contains("\MSAS"))
        {
            $InstanceName = $SubKey.Name.Split("\")[4]
            $RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
            $EXEPath = "$($RegistryKey.SQLBinRoot)\msmdsrv.exe";

            netsh advfirewall firewall delete rule name="MSAS - $($InstanceName)"
            netsh advfirewall firewall add rule name="MSAS - $($InstanceName)" dir=in action=allow program="$($EXEPath)"
        }
    }
}

Open in new window

https://www.experts-exchange.com/articles/31687/Windows-Firewall-as-Code.html

Did you check remote connections?

al4629740

ASKER

I ran the code in powershell and this is what I got:

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

PS C:\Users\Barbara> $SqlKey = Get-ChildItem -ErrorAction SilentlyContinue "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Serve
r";
PS C:\Users\Barbara> If ($SqlKey -ne $null)
>> {
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Server"
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Admin Connection"
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Database Management"
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Service Broker"
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Debugger/RPC"
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Server Browse Button Service"
>>     netsh advfirewall firewall delete rule name="MSAS - SQL Analysis Services"
>>     netsh advfirewall firewall delete rule name="MSAS - SQL Browser"
>>     netsh advfirewall firewall delete rule name="MSRS - HTTP"
>>     netsh advfirewall firewall delete rule name="MSRS - SSL"
>>
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Server" dir=in action=allow protocol=TCP localport=1433
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Admin Connection" dir=in action=allow protocol=TCP localpor
t=1434
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Database Management" dir=in action=allow protocol=UDP local
port=1434
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Service Broker" dir=in action=allow protocol=TCP localport=
4022
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=13
5
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Server Browse Button Service" dir=in action=allow protocol=
UDP localport=1433
>>     netsh advfirewall firewall add rule name="MSAS - SQL Analysis Services" dir=in action=allow protocol=TCP localpor
t=2383
>>     netsh advfirewall firewall add rule name="MSAS - SQL Browser" dir=in action=allow protocol=TCP localport=2382
>>     netsh advfirewall firewall add rule name="MSRS - HTTP" dir=in action=allow protocol=TCP localport=80
>>     netsh advfirewall firewall add rule name="MSRS - SSL" dir=in action=allow protocol=TCP localport=443
>>
>>     $SubKeys = Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";
>>     ForEach ($SubKey in $SubKeys)
>>     {
>>         If ($SubKey.Name.Contains("\MSSQL") -Anc !$SubKey.Name.Contains("\MSSQLServer"))
>>         {
>>            $InstanceName = $SubKey.Name.Split("\")[4]
>>            $RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
>>            $EXEPath = "$($RegistryKey.SQLBinRoot)\sqlservr.exe";
>>
>>            netsh advfirewall firewall delete rule name="MSSQL - $($InstanceName)"
>>            netsh advfirewall firewall add rule name="MSSQL - $($InstanceName)" dir=in action=allow program="$($EXEPat
h)"
>>         }
At line:28 char:45
+         If ($SubKey.Name.Contains("\MSSQL") -Anc !$SubKey.Name.Contai ...
+                                             ~~~~
Unexpected token '-Anc' in expression or statement.
At line:28 char:50
+ ...  ($SubKey.Name.Contains("\MSSQL") -Anc !$SubKey.Name.Contains("\MSSQL ...
+                                            ~~~~~~~~~~~~~~~~~~~~~~
Unexpected token '!$SubKey.Name.Contains' in expression or statement.
At line:28 char:50
+ ...  ($SubKey.Name.Contains("\MSSQL") -Anc !$SubKey.Name.Contains("\MSSQL ...
+                                            ~~~~~~~~~~~~~~~~~~~~~~
Missing closing ')' after expression in 'If' statement.
At line:27 char:5
+     {
+     ~
Missing closing '}' in statement block or type definition.
At line:2 char:1
+ {
+ ~
Missing closing '}' in statement block or type definition.
At line:28 char:88
+ ... .Name.Contains("\MSSQL") -Anc !$SubKey.Name.Contains("\MSSQLServer"))
+                                                                         ~
Unexpected token ')' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken

PS C:\Users\Barbara>         If ($SubKey.Name.Contains("\MSAS"))
>>         {
>>             $InstanceName = $SubKey.Name.Split("\")[4]
>>             $RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
>>             $EXEPath = "$($RegistryKey.SQLBinRoot)\msmdsrv.exe";
>>
>>             netsh advfirewall firewall delete rule name="MSAS - $($InstanceName)"
>>             netsh advfirewall firewall add rule name="MSAS - $($InstanceName)" dir=in action=allow program="$($EXEPat
h)"
>>         }
You cannot call a method on a null-valued expression.
At line:1 char:13
+         If ($SubKey.Name.Contains("\MSAS"))
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

PS C:\Users\Barbara>     }
At line:1 char:5
+     }
+     ~
Unexpected token '}' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken

PS C:\Users\Barbara> }

Open in new window

Shaun Vermaak

-Anc should be -And

al4629740

ASKER

btw, I can't check remote connections, because that requires me to login...which i can't

al4629740

ASKER

Results

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

PS C:\WINDOWS\system32> $SqlKey = Get-ChildItem -ErrorAction SilentlyContinue "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Se
rver";
PS C:\WINDOWS\system32> If ($SqlKey -ne $null)
>> {
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Server"
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Admin Connection"
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Database Management"
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Service Broker"
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Debugger/RPC"
>>     netsh advfirewall firewall delete rule name="MSSQL - SQL Server Browse Button Service"
>>     netsh advfirewall firewall delete rule name="MSAS - SQL Analysis Services"
>>     netsh advfirewall firewall delete rule name="MSAS - SQL Browser"
>>     netsh advfirewall firewall delete rule name="MSRS - HTTP"
>>     netsh advfirewall firewall delete rule name="MSRS - SSL"
>>
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Server" dir=in action=allow protocol=TCP localport=1433
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Admin Connection" dir=in action=allow protocol=TCP localpor
t=1434
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Database Management" dir=in action=allow protocol=UDP local
port=1434
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Service Broker" dir=in action=allow protocol=TCP localport=
4022
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=13
5
>>     netsh advfirewall firewall add rule name="MSSQL - SQL Server Browse Button Service" dir=in action=allow protocol=
UDP localport=1433
>>     netsh advfirewall firewall add rule name="MSAS - SQL Analysis Services" dir=in action=allow protocol=TCP localpor
t=2383
>>     netsh advfirewall firewall add rule name="MSAS - SQL Browser" dir=in action=allow protocol=TCP localport=2382
>>     netsh advfirewall firewall add rule name="MSRS - HTTP" dir=in action=allow protocol=TCP localport=80
>>     netsh advfirewall firewall add rule name="MSRS - SSL" dir=in action=allow protocol=TCP localport=443
>>
>>     $SubKeys = Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";
>>     ForEach ($SubKey in $SubKeys)
>>     {
>>         If ($SubKey.Name.Contains("\MSSQL") -And !$SubKey.Name.Contains("\MSSQLServer"))
>>         {
>>            $InstanceName = $SubKey.Name.Split("\")[4]
>>            $RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
>>            $EXEPath = "$($RegistryKey.SQLBinRoot)\sqlservr.exe";
>>
>>            netsh advfirewall firewall delete rule name="MSSQL - $($InstanceName)"
>>            netsh advfirewall firewall add rule name="MSSQL - $($InstanceName)" dir=in action=allow program="$($EXEPat
h)"
>>         }
>>         If ($SubKey.Name.Contains("\MSAS"))
>>         {
>>             $InstanceName = $SubKey.Name.Split("\")[4]
>>             $RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
>>             $EXEPath = "$($RegistryKey.SQLBinRoot)\msmdsrv.exe";
>>
>>             netsh advfirewall firewall delete rule name="MSAS - $($InstanceName)"
>>             netsh advfirewall firewall add rule name="MSAS - $($InstanceName)" dir=in action=allow program="$($EXEPat
h)"
>>         }
>>     }
>> }

No rules match the specified criteria.


No rules match the specified criteria.


No rules match the specified criteria.


No rules match the specified criteria.


No rules match the specified criteria.


No rules match the specified criteria.


No rules match the specified criteria.


No rules match the specified criteria.


No rules match the specified criteria.


No rules match the specified criteria.

Ok.

Ok.

Ok.

Ok.

Ok.

Ok.

Ok.

Ok.

Ok.

Ok.


No rules match the specified criteria.

Ok.


No rules match the specified criteria.

Ok.

PS C:\WINDOWS\system32>

Open in new window

al4629740

ASKER

I checked the services on the DB and they are not running. When I try to run them, I get an error message.

Shaun Vermaak

Results of PowerShell is correct. Check event viewer for other errors

al4629740

ASKER

From Event Viewer

Log Name:      System
Source:        Service Control Manager
Date:          3/2/2018 12:38:11 PM
Event ID:      7024
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      MOMsWORKPC
Description:
The SQL Server (MSSQLSERVER) service terminated with the following service-specific error: 
%%945
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Service Control Manager" Guid="{555908d1-a6d7-4695-8e1e-26931d2012f4}" EventSourceName="Service Control Manager" />
    <EventID Qualifiers="49152">7024</EventID>
    <Version>0</Version>
    <Level>2</Level>
    <Task>0</Task>
    <Opcode>0</Opcode>
    <Keywords>0x8080000000000000</Keywords>
    <TimeCreated SystemTime="2018-03-02T18:38:11.648723700Z" />
    <EventRecordID>5524</EventRecordID>
    <Correlation />
    <Execution ProcessID="752" ThreadID="952" />
    <Channel>System</Channel>
    <Computer>MOMsWORKPC</Computer>
    <Security />
  </System>
  <EventData>
    <Data Name="param1">SQL Server (MSSQLSERVER)</Data>
    <Data Name="param2">%%945</Data>
    <Binary>4D005300530051004C005300450052005600450052000000</Binary>
  </EventData>
</Event>

Open in new window

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

al4629740

ASKER

I have everything locally and I still can't connect to it

arnold

Check the sql server configuration, under the server instance make sure shared memory, named pipes and TCP/ip are enabled, you have to restart the services ....

Are you also failing to access the db running ssms locally on the server?

al4629740

ASKER

All are enabled. Yes I am failing access to the db from ssms.

The services won't turn on.

Kyle Abrahams, PMP

Did you try looking at the link for the blog?

http://www.gerjon.com/microsoft/error-starting-sql-instance-after-fresh-install/

arnold

First, access SQL server using ssms right on the server which eliminates much
....
Post the output from the server to the netstat -rn

arnold

Relooked at the error, note the ssms connection attempt is using named pipes.
Open ssms, before connecting look at the options and select TCP/ip and see whether the connection sets up.
Potentially the server\instance being requested, does not reach.

al4629740

ASKER

Please keep in mind that the problem seems to be that the services are not running on the server. I have verified that they are not working. I think thats the problem, I just can't figure how to turn it on.

Here is the output to netstat after I tried to login in futiity.

C:\Users\Barbara>netstat -rn
===========================================================================
Interface List
 16...a0 d3 c1 49 40 d5 ......This Qualcomm Atheros network Controller connects you to the network.
  7...00 ff 64 df f3 85 ......TeamViewer VPN Adapter
 13...9c ad 97 45 c1 19 ......Microsoft Wi-Fi Direct Virtual Adapter
  8...9c ad 97 45 c1 1f ......Ralink RT3290 802.11bgn Wi-Fi Adapter
 15...9c ad 97 45 c1 20 ......Bluetooth PAN HelpText
  1...........................Software Loopback Interface 1
===========================================================================

IPv4 Route Table
===========================================================================
Active Routes:
Network Destination        Netmask          Gateway       Interface  Metric
          0.0.0.0          0.0.0.0      192.168.0.1      192.168.0.2     25
        127.0.0.0        255.0.0.0         On-link         127.0.0.1    331
        127.0.0.1  255.255.255.255         On-link         127.0.0.1    331
  127.255.255.255  255.255.255.255         On-link         127.0.0.1    331
      192.168.0.0    255.255.255.0         On-link       192.168.0.2    281
      192.168.0.2  255.255.255.255         On-link       192.168.0.2    281
    192.168.0.255  255.255.255.255         On-link       192.168.0.2    281
        224.0.0.0        240.0.0.0         On-link         127.0.0.1    331
        224.0.0.0        240.0.0.0         On-link       192.168.0.2    281
  255.255.255.255  255.255.255.255         On-link         127.0.0.1    331
  255.255.255.255  255.255.255.255         On-link       192.168.0.2    281
===========================================================================
Persistent Routes:
  None

IPv6 Route Table
===========================================================================
Active Routes:
 If Metric Network Destination      Gateway
 16    281 ::/0                     fe80::b2b9:8aff:fed7:471c
  1    331 ::1/128                  On-link
 16    281 2601:244:5080:1120::/60  fe80::b2b9:8aff:fed7:471c
 16    281 2601:244:5080:1120::/64  On-link
 16    281 2601:244:5080:1120::4/128
                                    On-link
 16    281 2601:244:5080:1120:4042:19b:8110:2510/128
                                    On-link
 16    281 2601:244:5080:1120:5cdf:8ace:8175:338f/128
                                    On-link
 16    281 2601:244:5080:1120:69c5:2587:ebc0:644b/128
                                    On-link
 16    281 2601:244:5080:1120:a8e6:c538:73f1:e84d/128
                                    On-link
 16    281 2601:244:5080:1120:ad59:6b08:d1d0:1a50/128
                                    On-link
 16    281 2601:244:5080:1120:b0fe:384a:607a:a518/128
                                    On-link
 16    281 fe80::/64                On-link
 16    281 fe80::69c5:2587:ebc0:644b/128
                                    On-link
  1    331 ff00::/8                 On-link
 16    281 ff00::/8                 On-link
===========================================================================
Persistent Routes:
  None

Open in new window

al4629740

ASKER

And yes I am logging in to the server from ssms on the same machine. This is not happening over the network but everything is on the machine...

al4629740

ASKER

Kyle,

Your solution is working but then stalled when I tried the second command in the link. It was not connecting, but it started the server.

C:\WINDOWS\system32>SQLCMD -S .\MSSQLSERVER
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network Interfaces: Connection string is not valid [87]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Open in new window

al4629740

ASKER

After getting the server started I am one step closer to logging in. Here is the new message:

SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

al4629740

ASKER

So I got the server running with the help of you experts. The last error code I am receiving is attached as such. Is this just a simple login issue?

arnold

You are using a microsof online login, the SQL server can only be accessed using Microsoft local/ad domain account or SQL login.

Check the local users, you may have to use the SQL login, sa with the password you provided or create a new SQL login on the systemd ranting your SQL login with the rights you need to a specific db, or a Sysadmin role.

al4629740

ASKER

I setup a password during install. I’m pretty sure it was for sa although I don’t remember it specified that. Even when trying that password I get the same message. Is there any way I can create a new user?

SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial