Accessing SQL Server 2008 R2 Express with SQL Server Management Studio

sql-server-2008 ssms sql-server-2008r2-express
Elijha picture Elijha · Apr 10, 2011 · Viewed 8.7k times · Source

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?

Answer

p.campbell picture p.campbell · Apr 10, 2011

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 questions

How to find server name of SQL Server Management Studio

I installed Microsoft SQL Server 2008. When I start SQL Server Management Studio (SSMS), I get the Connect to Server login window with a blank textbox for Server name. I have tried a lot of names, but I couldn't solve it. …

Read More
How to connect to local instance of SQL Server 2008 Express

I just installed SQL Server 2008 Express on my Vista SP1 machine. I previously had 2005 on here and used it just fine with the old SQL Server Management Studio Express. I was able to connect with no problems to my PC-NAME\…

Read More
Sql Server 'Saving changes is not permitted' error ► Prevent saving changes that require table re-creation

When I create a table in SQL Server and save it, if I try to edit the table design, like change a column type from int to real, I get this error: Saving changes is not permitted. The change you …

Read More