Saturday, February 5, 2011

All the reasons I can't access an instance of SQL 2005

I've installed an instance of SQL 2005 Express on <computername>/SQLEXPRESS. There is only once instance installed. I've allowed remote connections, turned on SQL authentication, enabled TCP/IP, Named Pipes and VIA but I still can't access the database from another computer. I keep getting:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

What else can I look for? I'm sure my code is correct as it was used to connect to this same system prior to it being wiped. I'm pretty confident the connection string is correct as well:

Server=<computername>\SQLEXPRESS;User Id=<username>;Password=<password>;

There's also no firewalls standing between the two systems. They're on the same network segment and Windows Firewall has been shut off completely.

  • Is the SQL Server Browser running on the machine? For named instances, like \SQLExpress, the SQL Browser allows client machines to identify which port to connect to.

    By default, only the default instance runs on TCP 1433. If the client can't connect on the default port, it queries the SQL Browser at UDP 1434 to locate the correct port to use for a given named instance.

    Spencer Ruport : Ugh. Thank you. I didn't see a setting for that anywhere in SQL Management Studio or the Configuration Manager so I looked in services and it was disabled. Whatever happened to intuitive software??
    Jason Lepack : Better question, when did intuitive software begin?
    Spencer Ruport : Yeah I guess the days of messing with Autoexec.bat and config.sys weren't much better... and we didn't have Google back then either.

0 comments:

Post a Comment