Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2016 Setup - Connectivity Issues

Posted on 2016-11-29
4
Medium Priority
?
91 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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

604 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