Solved

SQL 2016 Setup - Connectivity Issues

Posted on 2016-11-29
4
17 Views
Last Modified: 2016-11-29
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
Comment
Question by:JamesonJendreas
  • 2
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 41906429
<<
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
 
LVL 1

Author Comment

by:JamesonJendreas
ID: 41906476
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
 
LVL 39

Expert Comment

by:lcohan
ID: 41906493
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
 
LVL 1

Author Comment

by:JamesonJendreas
ID: 41906503
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now