Start Free Trial

asked on

How to solve SQL Server 2008 R2 error, cannot connect to SQLSERVER error 53

I have just install SQL Server 2008 R2 and the installation went perfectly.

But, I cannot access the Database with SQL Server Management Studio, I have it set to mixed mode (SQL Server authentication, and Windows Authentication) but neither work.

I have set TCP/IP and Named Pipes to enabled in the SQL Server Configuration Manager, and am no longer sure what to do.

When i try to login using either Windows Authentication or SQL Server Authentication the error I receive is:
"Cannot connect to MSSQLSERVER.
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: 53)"

I think my computer may not be finding the database, when I go to '<Browse for more...>' when choosing the 'Server name:' it does not find the database.

If anyone can help that would be a great help.

Thanks

Did you install it to a default port or somewhere else?

ASKER

I'm not sure, I just went through the installation and that all went fine, then knew i had to change the TCP/IP to enabled, I don't recall it asking me to install it to a port... (sorry am new to sql server)

Perhaps go into control panel, admin tools, services and check to see if the browser service is started (or start it). Then see if it finds it when you do <browse for more>

ASKER

it was already started, SQL Server Agent has not been started though if that makes any difference?

Does your configuration look like this?
results.PNG

ASKER

yes it does, I think i mentioned that in my original post.

do i need to change anything with the 'SQL Native Client 10.90 Configuration'? add 'Aliases'?

No, a clean install without errors should have worked. Did you reboot and not login with the same admin account?

ASKER

I rebooted after the installation, and then logged into to the same account that I created it on.

actually I do remember one warning message that came up about my firewall and I'd need to open ports, but that would only effect this if i was trying to access this externally

Can you connect if you type

.\MSSQLSERVER

as your servername (period, backslash)
login.PNG

ASKER

ASKER

sorry wrong image! that was from earlier:

login.png

Can you try with ".\" instead of \\JDTaylor?

>>Invalid parameters found on names pipes<<
What does it look like? (see attached)
namedpipes.png

ASKER

and the .\ one doesn't work, see the double post above (accidently uploaded the wrong print screen!)
named-pipes.png

Does the servername appear in the drop down or did you have to type it in?

I am not sure... will look into a few things. Take a look at this in the meantime.

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/a98bee9a-3ed2-4d69-ad6b-06a1423ce6b6

ASKER

the servername only appears in there because I have typed it in previously so it remembers that, and when i click browse it doesn't display.
browse.png

ASKER

just had a quick read through there, this caught my attention:

"3. Before even 'trying' to use the management studio to connect on this last install, I made sure I installed 2008 SP1 first."

I tried installing SP1 earlier but it said it couldn't update my version - will try it again though.

ASKER

have tried again, it says 'There are no SQL Server instances or shared features that can be updated on this computer." so I don't need to update to SP1. any ideas?

I wonder if you only installed the management studio. You might try uninstall and reinstall (andy pay close attention to all the options that can be installed).

ASKER

I installed every option that was there, I have the enterprise edition so just ticked 'Select All' and everything was definetly ticked.

the MSSQLSERVER database is definetly installed on my computer as I can go directly to it:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

This is already the 3rd time that I have installed this, so I dont think its a problem with the installation

If you go into services, what do you have on the Log On tab for SQL Server 2008?
Local System account or a specific account?

ASKER

next to SQL Server(MSSQLSERVER) is 'Network Service'

Try changing it.

http://msdn.microsoft.com/en-us/library/ms143170%28SQL.90%29.aspx

>>We recommend that you do not use the Network Service account for the SQL Server. Local User or Domain User accounts are more appropriate for these SQL Server services.<<

ASKER

I changed it to 'Local System' then restarted the service, unfortunately it still doesn't work

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

thank you!!

It now works I had to follow these steps for anyone else reading who is having similar problems:

"Go to SQL Server Configuration manager->SQL native client configuration and under client protocols make sure named pipe is enabled.
Then go to Aliasses Tab->Right Click on it and select New Alias. In the Alias window, choose Named Pipes under the protocol option, provide the name of the machine(On which SQL is installed) on both "Alias and Server" box.

Open the SQL mamagement studio, In the connection box under server name type "NP:Servername" and try to connect.
To test TCP/IP connection you can do so by typing "TCP:Servername, Portnumber" (Do not include the double quotes)"

ASKER

I think I may have ended this question too early :(

I'm now logged onto the SQL Server Management Studio, but it seems I'm logged in as 'James-PC' and not 'MSSQLSERVER' and I'm assuming these are quite different things.

can i re-open this thread or do i need to create a new one?

Probably start a new one. First issue solved, now it is a new issue.(you are now in, just not as who you would like to be logged in as).

ASKER

just made a new question if you have any more ideas:

https://www.experts-exchange.com/questions/26367356/Problem-logging-into-SQL-Server-2008.html

What a Joke , ALL this just to Login. I have just had 3 hrs downtime going from 2008 express to R2 because SQL express 2008 it isnt smart enough to read an R2 database backup (another sick joke).

hey amenkes, I had this same issue. I was able to connect when I added a .\SQLSERVERNAME

Why is that?
Thanks