18

I've just installed SQL Server 2008 and I cannot connect to it with SQL Server Management Studio. My intention is to just let my local windows user be authenticated but I am not totally sure how to do that.

In the connect dialog I'm writing:

Server type: Database Engine
Server name: (local)
Authentication: Windows Authentication

My first question would be if that is what I should connect to? When I try to connect I get this error message:

TITLE: Connect to Server
------------------------------

Cannot connect to (local).

------------------------------
ADDITIONAL INFORMATION:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I went to the URL there displayed and it just basically says "be sure SQL server is running". I think it is but I am not totally sure.

I've disabled the Windows Firewall (this is Windows 7 7100 x86). I've also changed the log on system in the SQL Server Configuration Manager but it seems it's not a problem of logging in but not even be able to open the socket to it. On that same tool I've enabled all the protocols on "SQL Server Network Configuration" -> "Protocols for SQLEXPRESS" with no luck. I run out of ideas. What else can I try?

Pablo
  • 7,249
  • 25
  • 68
  • 83
  • Sounds like the true answer here was the named instance. .\SqlExpress dot meaning localhost and \SqlExpress is the instance that you want to connect to. For those not knowing, you can have more than one SQL Server on a machine. SqlExpress in this case, is just the name, rather than a strict indication of the type or strict verison of the product. – p.campbell May 29 '09 at 20:20

2 Answers2

23

Ok, can you open your services console and scroll down to S for SQL Server. You should now see the services. Please ensure SQL Server (SQLEXPRESS) is running and then try .\SQLEXPRESS instead of (local).

So as per your example:

Server type: Database Engine
Server name: .\SQLEXPRESS
Authentication: Windows Authentication

Hope this helps

Update: These instructions are because I assume you are running Express Edition not Dev/Std/Ent edition of SQL Server

Try ensuring the appropriate protocols are enabled:

  1. Start the SQL Configuration Manager (ie: Start->Programs->SQL Server->Configuration Tools)
  2. Expand the SQL native Client configuration
  3. Click Client Protocols (you may have a 32-bit and a 64-bit, apply to both)
  4. Ensure Shared memory, TCP/IP, Named Pipes are enabled in that order
  5. Expand SQL Server Network Configuration
  6. Ensure Shared Memory for either SQLEXPRESS and/or MSSQLSERVER is enabled
  7. Click SQL Server Services
  8. Restart any running services

You should now be able to login to the instance

If you find you cannot login at all you may need to follow these instructions to get SQL Server into single user mode. See here for the full instructions from Microsoft.

By default, sqlservr.exe is located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn. If a second instance of SQL Server is installed, a second copy of sqlservr.exe is located in a directory such as

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\binn.

You can start one instance of SQL Server by using sqlservr.exe from a different instance, but SQL Server will start the version of the incorrect instance as well, including service packs, which may lead to unexpected results.

To avoid this, use the MS-DOS change directory (cd) command to move to the correct directory before starting sqlservr.exe, as shown in the following example.

cd \Program Files\Microsoft SQL Server\MSSQL10_50.1\MSSQL\Binn

To start the default instance of SQL Server in single-user mode from a command prompt

From a command prompt, enter the following command:

sqlservr.exe -m

Single-user mode can be useful for performing emergency maintenance when you do not want other users to connect to SQL Server, but any user can become the single user, including the SQL Server Agent service.

You should now be able to login to the instance and add yourself to the security tab and grant full access.

Alternate Method:

THere is a script here that claims to add the current user to the SQL Server sysadmin role. This may work in single user mode but I have not verified it

Wayne
  • 3,084
  • 1
  • 21
  • 16
  • Is the Services Console the Services tab on the Windows Task Manager? (Win7). Anyway, SQLEXPRESS is not there, how do I run it? – Pablo May 24 '09 at 10:08
  • 1
    Service console is start->control panel->administrative tools->services – Wayne May 24 '09 at 10:09
  • Never mind that, I've found the Services console, SQLEXPRESS is running. – Pablo May 24 '09 at 10:11
  • Connecting to .\SQLEXPRESS did the trick. Thank you a lot Wayne! – Pablo May 24 '09 at 10:12
  • 1
    Thank you so much... was going crazy trying to figure this out! (Note to others in ".\SQLEXPRESS" ... "SQLEXPRESS" is the instance Id you gave the server during install. – RiddlerDev Mar 12 '11 at 02:37
  • @Wayne - I have both "SQL Server(SQLExpress)" and "Sql Server(MSSQLServer)" I could log into the express bu not the other one. I also want to try to get (local) running any suggestions? – chobo2 Jan 17 '12 at 18:50
  • @chobo2 have you gone into services and ensured both instances are running? – Wayne Jan 17 '12 at 22:11
  • @Wayne - yes both instances are running. – chobo2 Jan 18 '12 at 06:12
  • @chocobo - (local), . [single full-stop], 127.0.0.1 and can all be used to connect to local default instance of SQL Server. Try any of those and confirm you get the same error. Also ensure your firewall is disabled just to confirm is not a networking issue. Also, is this from Management Studio? – Wayne Jan 18 '12 at 09:43
  • @Wayne - Ya I tired all those above and turned off the firewall. This is from Management Studio 2008. – chobo2 Jan 25 '12 at 17:30
  • @Chobo2 - Is this a fresh install and did you make sure you were added to the appropriate roles during install? – Wayne Jan 25 '12 at 23:25
  • No it is not a fresh install. What happened was I locked my windows account came back a bit later and tried to log in and all of a sudden it decided to make me a new account profile and I was never able to access may account again. – chobo2 Jan 26 '12 at 03:14
  • @Chobo2 - Added instructions for Single User Model of SQL Server. This should help you restore your account authentication if this is truly the problem. – Wayne Jan 26 '12 at 21:45
  • @Wayne - Ok I tried that but still can't connect to (local). I tired connecting to "MSSQLSERVER" but I get "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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)" but I am unsure if this was always what I was getting. – chobo2 Jan 26 '12 at 22:00
  • @chobo2 - Added update on configuration of protocols for SQL Server – Wayne Feb 17 '12 at 14:31
0

(local) resolves to 127.0.0.1, and works for defaults only. Instances, like the default for SQL Server Express, need a real name. Note that you can check the error log for SQL Server (SQL install folder, then log) and one of the first few entries will give you the IP, port, and name of the instance.

So you could use those instead. Instance names are good to use, since they are fairly consistent. Note that they are also use dynamic ports, so it could be a different port on each restart. SQL Server configuration Manager can set a specific port. not sure about the Express tools.

Steve Jones
  • 795
  • 5
  • 8