Solved

SQL 2016 Setup - Connectivity Issues

Posted on 2016-11-29
4
66 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 40

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 40

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

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!

Question has a verified solution.

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

Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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