DNS solution for sql server running in named instance or different port
-
Thanks for your time and help with this.
From the application log in event viewer, trying to start the SQLTEST SQL Server service when "Listen All" is set to no:
Error 1: Server failed to listen on 10.10.10.149 <ipv4> 1433. Error: 0x2741. To proceed, notify your system administrator.
Error 2: TDSSNIClient initialization failed with error 0x2741, status code 0xa. Reason: Unable to initialize the TCP/IP listener. The requested address is not valid in its context.
Error 3: TDSSNIClient initialization failed with error 0x2741, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The requested address is not valid in its context.
Error 4: 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.
Error 5: SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
The SQL Log is as follows:
2011-10-18 13:07:46.26 Server Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
2011-10-18 13:07:46.26 Server (c) Microsoft Corporation.
2011-10-18 13:07:46.26 Server All rights reserved.
2011-10-18 13:07:46.26 Server Server process ID is 2820.
2011-10-18 13:07:46.26 Server System Manufacturer: 'Microsoft Corporation', System Model: 'Virtual Machine'.
2011-10-18 13:07:46.26 Server Authentication mode is MIXED.
2011-10-18 13:07:46.26 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLTEST\MSSQL\Log\ERRORLOG'.
2011-10-18 13:07:46.26 Server This instance of SQL Server last reported using a process ID of 2816 at 18/10/2011 1:05:38 PM (local) 18/10/2011 2:05:38 AM (UTC). This is an informational message only; no user action is required.
2011-10-18 13:07:46.26 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLTEST\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLTEST\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLTEST\MSSQL\DATA\mastlog.ldf
2011-10-18 13:07:46.27 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-10-18 13:07:46.27 Server Detected 1 CPUs. This is an informational message; no user action is required.
2011-10-18 13:07:46.27 Server Cannot use Large Page Extensions: lock memory privilege was not granted.
2011-10-18 13:07:46.34 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2011-10-18 13:07:46.36 Server Node configuration: node 0: CPU mask: 0x0000000000000001:0 Active CPU mask: 0x0000000000000001:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2011-10-18 13:07:46.39 spid7s Starting up database 'master'.
2011-10-18 13:07:46.50 spid7s Resource governor reconfiguration succeeded.
2011-10-18 13:07:46.50 spid7s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2011-10-18 13:07:46.50 spid7s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2011-10-18 13:07:46.51 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLTEST'.
2011-10-18 13:07:46.54 spid7s SQL Trace ID 1 was started by login "sa".
2011-10-18 13:07:46.54 spid7s Starting up database 'mssqlsystemresource'.
2011-10-18 13:07:46.55 spid7s The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
2011-10-18 13:07:47.18 Server A self-generated certificate was successfully loaded for encryption.
2011-10-18 13:07:47.18 Server Error: 26024, Severity: 16, State: 1.
2011-10-18 13:07:47.18 Server Server failed to listen on 10.10.10.149 <ipv4> 1433. Error: 0x2741. To proceed, notify your system administrator.
2011-10-18 13:07:47.18 Server Error: 17182, Severity: 16, State: 1.
2011-10-18 13:07:47.18 Server TDSSNIClient initialization failed with error 0x2741, status code 0xa. Reason: Unable to initialize the TCP/IP listener. The requested address is not valid in its context.
2011-10-18 13:07:47.18 Server Error: 17182, Severity: 16, State: 1.
2011-10-18 13:07:47.18 Server TDSSNIClient initialization failed with error 0x2741, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The requested address is not valid in its context.
2011-10-18 13:07:47.19 Server Error: 17826, Severity: 18, State: 3.
2011-10-18 13:07:47.19 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.
2011-10-18 13:07:47.19 Server Error: 17120, Severity: 16, State: 1.
2011-10-18 13:07:47.19 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
-
With the SQL server down do a netstat -an
look for any entries that reference port 1433 such as TCP 0.0.0.0:1433, I'm guessing there is something already listening on that port.
CEWII
-
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING
This appeared there as you suggested, I stopped the default instance (which I had forgotten was still running) and it no longer appeared. Still get the same error trying to start the sqltest instance though.
-
So do you see anything like TCP 10.10.10.149:1433 already? Do you see ANY references to TCP port 1433?
CEWII
-
None at all. I might not have been clear that 10.10.10.149 is not the IP address of the machine, it's the IP address I attempted to point at the instance through the DNS entry. I don't see any entries to 10.10.10.149 at all, and I don't see any entries to port 1433 either.
Even after starting the default instance back up again there's nothing listening (or established) on 1433
EDIT:
If I change the TCP/IP Properties for SQLTEST to be "Listen All" : Yes, I can start the service and I get:
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING
TCP [::]:1433 [::]:0 LISTENING
But this defeats the purpose? It is no longer listening for 10.10.10.149 specifically which is the whole point of the exercise as I understand it?
-
There is a way to get multiple instances on a single machine to all use the same port number, but you need to plan for this before you install SQL Server.
You need to install Windows clustering, and treat the server as a single-node cluster. You then install each of your instances as single-node SQL Server clusters. This gives you no fail-over capability, but you can always add it in the future if you need it.
Each SQL Server cluster requires its own IP address, and will listen on port 1433. You can then create vanity DNS names that point to the cluster IP addresses, so that if you move a SQL cluster to a different server the vanity name can be repointed to the new IP address without any impact to your application.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Foulcon:
First add that IP address to the machine itself, then you should be able to make this work.
EdVassie:
This might work in SQL 2005 but it won't work with 2008 clustering, I recently looked into it myself, in 2008 clustering it requires listen on all, given that you can't share port 1433. As a side point, what you are describing is not everything sharing port 1433, that is not possible. What you are describing is each instance on port 1433 getting its own IP address. which is possible.
CEWII
-
Thank you very much for you assistance Elliott, that was the issue. I had not realised I needed to (or even could) add extra IP addresses to a single nic, I was working off the vague notion that having the alias set in SQL and the host in DNS it must somehow link them. After doing that I am able to connect to the server using the alias.
-
Great, yw.
CEWII
-
Some additional notes. In order to get the SQL Server Agent to start I had to put the tcp protocol for the instance back to "Listen All : yes" long enough to start the Agent, then in SSMS go to properties, Connection page, and set the "Alias local host server" to the instance name (testinstance in this case). After that I could turn listen all back off and the agent would start (this might be because we disable named pipes by default, they've given us problems in the past with a third party database).
Then I had an issue where I couldn't connect to the instance when using the alias through SSMS on the actual server using windows authentication (I could connect to it using SQL authentication or by using the full instance name with windows auth, I could also connect to the alias with SSMS and windows auth on my own PC). Followed the instructions on this blog[/url] and did the following:
The solution is to go into the registry and go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa. Once you get there, add a REG_DWORD named DisableLoopbackCheck, and give it a value of 1.
And finally another problem which seems related to this process (because it doesn't happen if I try on the default instance):
I tried to copy a small database across to the new instance to ensure that our applications would also be able to connect to the instance but using the Copy Database wizard produced the above error. For now I"m just going to look at restoring a backup to make sure it's working before trying to fix this error.
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply