• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 100
  • Last Modified:

SQL 2016 Setup - Connectivity Issues

I am in the process of setting up a small 2016 lab for dev/testing.  I have build a set VM's with a Domain Controller, AD FS, SQL and SCOM and in test domain.  All servers are running 2016.  This is not a production environment.

I am trying to get the SCOM server to communicate with the SQL server over the default 1433 MS-SQL port.   I feel like I am missing something easy, but here are the things that I have checked:
1. Servers are on the same subnet (same vm host at that), no hardware firewall
2. Servers can ping eachother.  I did create a Windows firewall rule to allow ICMP between the hosts.  I also created one for the SQL server (1433)
3. Confirmed named pipes are enabled in SQL Configuration Manager
4.  I set "IP3" to "Enabled" on  the TCP/IP protocol network properites on Configuration Manager - IP3 is my private routable IP interface
5. Confirmed remote access was enabled under Connections in Management Studio on the local SQL server
6. Running a wireshark on the SQL server while trying to access the SQL server from the SCOM (either by ODBC setup or using Management Server on SCOM server) - I see inbound packets from the SCOM server (both over 1433 and ICMP) but the SQL server never sends back a packet.
7. PortQry results in a FILTERED message
 
It seems as though the SQL server is ignoring the requests.  I would assume this is a security setting that I am missing.

[edit]: Forgot to mention - I've tried connecting with both the FQDN and IP address.  Connecting wiht Windows Authentication.  I am going to enable/setup a local SA account for testing

Cheers
JJ
0
JamesonJendreas
Asked:
JamesonJendreas
  • 2
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
<<
unning a wireshark on the SQL server while trying to access the SQL server from the SCOM (either by ODBC setup or using Management Server on SCOM server)
>>

What login credentials did you used in the ODBC data source? maybe you can test it via a ODBC dsn using different logins like a AD one and a SQL created login. I believe you have some sort of authentication issue not necesarily connectivity issue if you can create a ODBC dsn and Test Connectivity...

Also SQL needs UDP 1434 to be opened if you connect via named pipes by name not by IP
0
 
JamesonJendreasAuthor Commented:
Thanks for the reply!  I have tried both AD/Windows authentication using the domain administrator login (and I can login locally on the SQL server) as well as create a local SA account.  I added port 1434 to my ACL, no change.  

I agree it's not going to be network as shown by the wireshark.
0
 
lcohanDatabase AnalystCommented:
Is that "AD/Windows authentication using the domain administrator login" listed under SQL Server Security - Logins and is it part of the sysadmin group? and you say that "as well as create a local SA account' - that SA already exists in SQL Server and is by default part of the sysadmin SQL Server Role - right?
0
 
JamesonJendreasAuthor Commented:
I enabled local authentication and enabled the SA account and reset it's password.  That didn't seem to work, but a restart of the server (when in doubt!) I started getting an authentication error on login.  I tried again with my domain credentials, and voila!   Not too sure what 'kicked in',  I had restarted the services (SQL, firewall) after prior changes, but this one seemed to do it.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now