IRL #2 – Connectivity Issues
Problem: Application owner is trying connect to SQL Server to create a database, connection failed. Error message suggests checking Instance Name and “Allow Remote Connections” option set to True.
Background: This client has a wide variety of SQL Servers in Prod, Test and Dev. Many different versions and editions, quite a few coming from 3rd party vendors. This was in a new test environment for an application already up and running in Prod.
Resolution Attempts:
- Try to Connect via IP address – failed
- Verify Instance name – verified
- Verify “Allow Remote Connections” is set to 1/True – it is
- Verify SQL Browser Service Running – discovered Set to Disabled
- Verify TCP/IP network protocol enabled – discovered disabled
Solution:
- Enable TCP/IP for Named Instance SQLEXPRESS03
- Restart Instance
- Set SQL Browser Service to Automatic and start
- Test logon via UDL file – success
Lessons you can benefit from:
- SQL Express default install only enables Shared memory connections
- Error messages are not always going to give you complete information
- Have a process for testing
This entire process took about 10 minutes, including the instance restart. There are 3 other Express edition installs on this server.
Other posts in the IRL – In Real Life series:
https://dallasdbas.com/category/sql/irl/
Thanks for reading!
