cannot consistently connect to SQL Server named instance
I built a SQL Server with 2-instances, the default and a named instance. The application is connecting to the named-instance and connects fine most of the time. But then the next morning or after a bunch of reboots, the application cannot connect to the named-instance. However, connecting to the default instance is never a problem. I'm not sure if it's a Firewall issue, a WIN issue and/or a SQL config issue. Connecting locally to the named-instance is never a problem. The problem is connecting remotely. And yes the server is configured to allow for remote connections. I am a DBA, so I have info on the db server. Below are the named-instance configs and attached is the error when trying to connect to the named-instance via a remote machine. Please let me know what this might be.
The following are the configs:
01. default instance: 1433 for TCP
02. firewall allows for 1433 TCP
03. named instance: 1434 for UDP
04. firewall allows for 1434 UDP
05. named instance: 1435 for TCP
06. firewall allows for 1435 TCP
07. SQL Server Network Config -> Protocols for InstanceName -> TCP/IP Properties -> IP Addresses:
- IPAll -> TCP Dynamic Ports set to blank
- IPAll -> TCP Port set to 1435
08. added SSIS, DBE and SQL Agent service-accounts to 'log on as a service'
09. will be adding [NT AUTHORITY\LOCALSERVICE], i.e. the SQL Browser Service to 'log on as a service' as well
- this is not done yet, but will be done tomorrow
- no errors in log for this, but it is required according to BOL
10. rebooted server
Thanks,
pae2
ConnectError.jpg
The following are the configs:
01. default instance: 1433 for TCP
02. firewall allows for 1433 TCP
03. named instance: 1434 for UDP
04. firewall allows for 1434 UDP
05. named instance: 1435 for TCP
06. firewall allows for 1435 TCP
07. SQL Server Network Config -> Protocols for InstanceName -> TCP/IP Properties -> IP Addresses:
- IPAll -> TCP Dynamic Ports set to blank
- IPAll -> TCP Port set to 1435
08. added SSIS, DBE and SQL Agent service-accounts to 'log on as a service'
09. will be adding [NT AUTHORITY\LOCALSERVICE], i.e. the SQL Browser Service to 'log on as a service' as well
- this is not done yet, but will be done tomorrow
- no errors in log for this, but it is required according to BOL
10. rebooted server
Thanks,
pae2
ConnectError.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is the named instance port static? Because by default is a dynamic port so it can change between restarts.
Vitor, 07 shows a static IPAll setting.
Dump question-- does your SQL server have a static ip and fixed ports to the services you're try to run. ---- I know nothing about SQL but this seems like a connection issue which is right up my ally.
Here is another dump question---Does your dns knows where your sql server should be, it should have a A or PTR record.
When dns knows nothing, then nothing can be found.
But take it with a grain of salt from an SQL ignorant
You normally get a message like that when either port mismatch, IP address change or wrong setup to begin with
Here is another dump question---Does your dns knows where your sql server should be, it should have a A or PTR record.
When dns knows nothing, then nothing can be found.
But take it with a grain of salt from an SQL ignorant
You normally get a message like that when either port mismatch, IP address change or wrong setup to begin with
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
- Muhammad the server is joined in the domain - thanks!
- Vitor, Qlemo was correct; that is referenced in 07, it is static - thanks!
- Natty, yes static/fixed for SQL; DNS is also good - thanks!
pae2
- Vitor, Qlemo was correct; that is referenced in 07, it is static - thanks!
- Natty, yes static/fixed for SQL; DNS is also good - thanks!
pae2
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Qlemo - will be trying these steps tomorrow in the office and hopefully close this question then as well. Thanks again!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Vitor, the direct connection using the port number instead of the instance name works, so it is not the SQL Server port but the SQL Browser function which fails, obviously because of issues with Windows Firewall (but that is still not confirmed 100%).
(Note: [SERVER\INSTANCENAME, 1435] is the same as [SERVER, 1435], the instance name is ignored.)
A. Connecting via [SERVER\INSTANCENAME, 1435] DOES work for remote connections.
B. Connecting via [SERVER\INSTANCENAME] DOES NOT work for remote connections.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if not so double check that at the client side it is reachable via hostname instead of IP, like if Server's name is 'SQLSERVER' having 192.168.x.x IP. So it should be in reach via Host Name from client.