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!

error
al4629740Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun VermaakTechnical SpecialistCommented:
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?
SQLServer2008_ServerProperties_1-1-.png
al4629740Author Commented:
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 VermaakTechnical SpecialistCommented:
-Anc should be -And
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

al4629740Author Commented:
btw, I can't check remote connections, because that requires me to login...which i can't
al4629740Author Commented:
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

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

error.gif
Shaun VermaakTechnical SpecialistCommented:
Results of PowerShell is correct. Check event viewer for other errors
al4629740Author Commented:
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

Kyle AbrahamsSenior .Net DeveloperCommented:
I found this blog that had the same issue with steps to fix it:
http://www.gerjon.com/microsoft/error-starting-sql-instance-after-fresh-install/

It's for SQL Server Express but may apply to you.

Try logging into the remote server and running everything locally for now.
(Or can you connect to it locally if you run SSMS on the server)?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
I have everything locally and I still can't connect to it
arnoldCommented:
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?
al4629740Author Commented:
All are enabled.  Yes I am failing access to the db from ssms.

The services won't turn on.
Kyle AbrahamsSenior .Net DeveloperCommented:
arnoldCommented:
First, access SQL server using ssms right on the server which eliminates much
....
Post the output from the server to the netstat -rn
arnoldCommented:
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.
al4629740Author Commented:
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

al4629740Author Commented:
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...
al4629740Author Commented:
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

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

error.gif
arnoldCommented:
Your server config seems to require encryption. Check the additional connection parameters and either add the Certificate the server presents as trusted or disable verification.
al4629740Author Commented:
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?

error.gif
arnoldCommented:
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.
al4629740Author Commented:
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?
arnoldCommented:
You ace to access the SQL server directly while on the server. Make sure your server runs in mixed mode, Windows  and SQL login.
Under the database there is the sevurity this is where you create SQL logins, then you need to grant it rights on databases or make it part of the Sysadmin role.

Confirm your sa password by opening a second connection to the local db using SQL login a with the password.
After creating the SQL login, test locally to make sure you have not made an error and have all the access you need.

If you still get an error, identifying why narrows the task..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.