Solved

SQL 2016 Setup - Connectivity Issues

Posted on 2016-11-29
4
54 Views
Last Modified: 2016-12-28
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 24
SSRS - Date Report Options 2 29
Index and Stats Management-Specific tables 8 23
Error when creating a table from a function 6 20
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

830 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