Accessing SQL Server 2008 R2 Express with SQL Server Management Studio

sql-server-2008sql-server-2008r2-expressssms

OK maybe I'm being stupid somehow, and my googleing has not found a clear step by step user case for beginners.

I have just installed into my Win 7 Pro 64-bit SP1 machine SQL Server 2008 R2 Express Database with Advanced Services [downloaded from here http://www.microsoft.com/express/Database/InstallOptions.aspx]. (Actually I have removed and installed it 3 times now to check I'm not missing anything config wise). I have left basically all settings as default, with the exception of calling the Instance SQLEXPRESS (all caps vs default SQLexpress) my last installation I have left set as 'windows authentication mode' with only the same user as I am running now selected (only one user on this machine anyway).

After completion I have restarted the machine, I can see the services

  • SQL Server (SQLEXPRESS) -> Started -> Automatic
  • SQL Server Agent (SQLEXPRESS) -> -> Manual
  • SQL Server Browser -> -> Manual
  • SQL Server Reporting Service (SQLEXPRESS) -> Started -> Automatic
  • SQL Server VSS Writer -> Started -> Automatic

.. So it seems to be up OK.

As part of the installation process it also installed SQL Server Management Studio 2008 R2 and when I try and connect I am choosing: 

Server: Database Engine
Servername :  \SQLEXPRESS
Authentication: Windows Authentication
User name:  (selected for me and not alterable) machinename\username
Password: (also blank greyed out)

I hit connect and get the following message.

Cannot connect to \SQLEXPRESS.
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: 25 – Connection
string is not valid) (.Net SqlClient
Data Provider)

Error Number: 87
Severity: 20
State: 0

OK, so given I am not entering a username or password, and I'm certain of the server instance name – and that it is running … what am I doing wrong?

Best Answer

Here are a few troubleshooting tips for connecting to SQL Server via SSMS:

  • Ensure you're connecting to .\sqlexpress or localhost\sqlexpress, or (local)\sqlexpress. It's not case sensitive. It appears you're missing the hostname in the server name field in the connection dialog.
  • You're using Windows/AD authentication, so you don't have to enter any credentials. It appears you're all set there.
Related Topic