Using certificates with SQL server

-> Install SDK from https://msdn.microsoft.com/en-us/windows/desktop/bg162891.aspx to get makecert.exe.

-> Makecert.exe will be present in location C:\Program Files (x86)\Windows Kits\8.1\bin\x64\ for X64 and C:\Program Files\Windows Kits\8.1\bin\x86\ for X86.

-> When using makecert.exe, make sure you use the correct server FQDN. Any mistakes in the FQDN while creating the certificate will result in the certificate not being used by SQL server.

-> Run the below query from command prompt.
makecert -r -pe -n “CN=<Server_Name_FQDN>” -b 02/13/2017 -e 12/01/2040 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine  -sky exchange -sp “Microsoft RSA SChannel Cryptographic Provider” -sy 12

-> Click on Run -> MMC -> File -> Add/remove snap in -> Click on Certificates -> Add it.

-> Select “Computer account” -> Finish.

-> Select Personal -> Certificates -> Look at the certificate you just created -> Double click it.

-> Click on Details -> Go to thumbprint. You will see something like below,
‎e0 fd 68 2a af 72 74 fb 24 24 ea 77 99 60 85 58 e1 61 79 1e

-> Remove the spaces and place it in a notepad session.

-> Open registry and check the certificate key that resides in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.<InstanceName>\MSSQLServer\SuperSocketNetLib and see if the values in “certificate key” is same as the one placed in the notepad session. If the “certificate” key in the registry is blank or it has a different value, then copy the thumbprint value from the notepad session and put it in the “Certificate” key in registry.

-> Open the SQL server configuration manager and get to certificate tab as shown below. For a Standalone SQL server instance, if everything is fine with the certificate configuration. You should see it in the certificate as shown below.

blog11_5

-> In case if it is a clustered SQL server instance, you wont see the certificate in the configuration manager as the certificate will be created using virtual host name and not with the physical node name.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Server TCP provider failed to listen on [1519]. Tcp port is already in use.

-> I was working on a 2-NODE cluster with physical nodes SERVER_A and SERVER_B. The SQL server instance SERVER_C\IN01 was not coming online in node SERVER_B.

-> SQL server version below,
Microsoft SQL Server 2005 – 9.00.5000.00 (Intel X86)
Dec 10 2010 10:56:29
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

-> I tried failing over SQL server instance from SERVER_B to SERVER_A to check if SQL server comes online on SERVER_A, the SQL services  failed on node SERVER_A as expected.

-> Opened the SQL server error log and found the below,

2016-12-01 23:27:12.73 Server      Server is listening on [ <IP_Address> <ipv4> 1519].
                2016-12-01 23:27:12.74 Server      Error: 26023, Severity: 16, State: 1.
                2016-12-01 23:27:12.74 Server      Server TCP provider failed to listen on [ 10.65.78.24 <ipv4> 1519]. Tcp port is already in use.
2016-12-01 23:27:12.74 Server      Error: 17182, Severity: 16, State: 1.
2016-12-01 23:27:12.74 Server      TDSSNIClient initialization failed with error 0x2740, status code 0xa.
2016-12-01 23:27:12.74 Server      Error: 17182, Severity: 16, State: 1.
2016-12-01 23:27:12.74 Server      TDSSNIClient initialization failed with error 0x2740, status code 0x1.
2016-12-01 23:27:12.74 Server      Error: 17826, Severity: 18, State: 3.
2016-12-01 23:27:12.74 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2016-12-01 23:27:12.74 Server      Error: 17120, Severity: 16, State: 1.
2016-12-01 23:27:12.74 Server      SQL Server could not spawn FRunCM thread.

-> It seems that TCP port is in use. Tried running netstats -Aon and found that the port 1519 was not in use.

-> Tried checking the SQL configuration manager for any aliases and found none on SERVER_A.

-> Checked the Configuration Manager -> Network Configuration -> Protocol for the Instance -> TCP/IP -> Properties -> I was able to see port 1519 set for all IP’s and TCP Dynamic Port / TCP port as well.

-> Removed the port 1519 from TCP Dynamic Port and started the SQL services and was able to see the same error, PORT IN USE!

-> Tried removing again and started the SQL services and the result was same. When tried refreshing it, the values were present for both Dynamic Port and TCP port. Understood that registry check pointing might cause this.

-> Ran the below command in elevated command prompt to disable registry checkpoint.

cluster res “SQL Server (IN01)” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER”

-> Removed the port 1519 from TCP Dynamic Port and started the SQL services and this time SQL services started.

-> Ran the below command from an elevated command prompt enable registry checkpoint.

cluster res “SQL Server (IN01)” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER”

-> I failed over the SQL server instance from SERVER_A to SERVER_B to check if SQL comes online on SERVER_B node.

-> The SQL services failed on node SERVER_B.

-> Checked the SQL server error log and did not get much info. Checked the event viewer and was able to understand that SQL services fails while trying for a connection. Suspected this could be due to alias issue.

-> Checked the configuration manager on server SERVER_B and as expected there  was an alias with below details.

Alias name : SERVER_C\IN01
Server name : SERVER_C\IN01
Port : 1476

-> I changed the port number to 1519 in the alias and the SQL services started as expected.

Thank You,
Karthik Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Hide Instance set to YES in SQL server Network Configuration

-> I was working on a 2 node cluster server where SQL server was failing to come online.

-> I tried starting the SQL services as a standalone service, that is tried starting the SQL services from services.msc or SQL server configuration manager and it started fine.

-> Started the SQL services from cluster administrator and it started to loop within the group on the physical node 1 and it failed.

-> I failed over the SQL server to physical node 2. All the resources came online.

-> I failed back to physical node 1 and the SQL server resources failed again.

-> Checked the SQL server errorlog and did not find any errors in it. I generated the cluster.log and found the below entry,

00001eb4.00003870::2016/11/31-01:43:04.009 INFO  [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] SvcTerminate: Service is stopped.
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ODBC sqldriverconnect failed
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ODBC sqldriverconnect failed
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] checkODBCConnectError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ODBC sqldriverconnect failed
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
00001eb4.00002d14::2016/11/31-01:43:35.116 INFO  [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ConnectToSQL: asked to terminate while trying to connect to server.
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] OnlineThread: Error connecting to SQL Server.
00001eb4.00002d14::2016/11/31-01:43:35.116 INFO  [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] OnlineThread: asked to terminate while waiting for QP.
00001eb4.00002d14::2016/11/31-01:43:35.116 WARN  [RHS] returning ResourceExitStateTerminate.
00000654.00003c78::2016/11/31-01:43:35.116 INFO  [RCM] HandleMonitorReply: TERMINATERESOURCE for ‘SQL Server (Instance_Name)’, gen(8) result 0.
00000654.00003c78::2016/11/31-01:43:35.116 INFO  [RCM] TransitionToState(SQL Server (Instance_Name)) [Terminating to Failed]–>Failed.
00000654.00003c78::2016/11/31-01:43:35.116 INFO  [RCM] rcm::RcmGroup::UpdateStateIfChanged: (<NODE1>, Pending –> Failed)

-> I tried starting the SQL service as a standalone service and tried connecting the SQL server using management studio. Could not connect to the SQL server instance.

-> It seems like SQL services starts fine, but cluster services cannot connect to the instance.

-> Opened SQL server configuration manager and checked for any bad aliases. Could not find any aliases created.

-> Opened the SQL server configuration manager. Opened the “Protocols for <Instance_Name>” under “SQL server network configuration”. “Hide Instance” was set to Yes. Please refer https://msdn.microsoft.com/en-us/library/ms179327(v=sql.110).aspx for more info on hide instances.

blog4_1

-> I changed “Hide Instance” to NO. Started the SQL services on cluster administrator and it started fine.

-> I tried failing over and failing back the SQL services between the 2 nodes and everything was online.

Thank You,
Karthik Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.