SQL Server Connection Problems

Question

I am running Windows 7 and have just installed SQL Server Connection R2 on my machine. I'm able to logon to the SQL server at the local college where I have a userid/pwd setup BUT am unable to logon locally. I am attempting to use Windows authentication locally and SQL authentication on the remote server.

I did not setup a userid when installing SQL server on my machine. Did I need to setup a local administrator userid when I did the install?

I am very hesitant to uninstall. Is there a way I can setup a local userid/pwd combination or if not, what do I do to fix this? This is not the case of my not using the right password. THis is a brand new WIndows 7 machine and I have not set up a password on this machine yet.

Here's the error: 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.

I've read the other other posts but know for sure I am not typing the password wrong as there is no machine password on this machine yet.

Can you help?

Answer 1

Hi,

I'm a little confused by your post, so just to confirm:

You have installed a version of SQL Server on your local machine and are trying to connect to it using Windows Authentication?

Your college is on a remote server and you can connect to that using SQL Server authentication.

If I've got that correct, the 2 machines are independent of one another and may have completely different authentication modes.

What version of SQL server did you install on your local machine?  If it's SQL Express, you need to speficy the instance name when connecting to it, i.e. mymachine\sqlexpress.  Given the error you're getting, I'd start with this.

Also, see -

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 2

Also, how are you trying to connect to SQL Server? Are you using SQL Server Management Studio, or sqlcmd, or something else?

You might find this short tutorial helpful Tutorial: Getting Started with the Database Engine

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 3

Hi Andrew!

Yes, I have SQL Server 2008 R2 installed on my machine and I am trying to connect locally using Windows Authentication. And also yes, I am able to connect to the local college using SQL authentication with no problem.

I installed SQL server 2008 R2. The instance that comes up in my Connect box has my machine name \ my user name but both this box and the password box are grayed out so I am unable to change the instance name or even enter a password. And when I try to connect I receive an error.

When I Cancel from that connect dialog box and then try to Right click on registered servers I receive a message that no servers are running. But when I go to the command prompt and type in sqlcmd -L I do see my machine name returned. When I go into Server Configuration Manager it does list SQLExpress and does say it's state is Running. Does any of this help?

Answer 4

Hi Rick!

Yes, this is a good tutorial, thank you! I followed the instructions and tried to see if my database server was running. When I get to the Connect dialog box, just so you know, the login area (userid and pwd) are grayed out. So based on the tutorial, I selected Cancel from that screen. I then used Right click on registered servers and received a message that no servers are running. But when I go to the command prompt and type in sqlcmd -L I do see my machine name returned. When I go into Server Configuration Manager it does list SQLExpress and does say it's state is Running. Does any of this help?

I also had a response from Andrew and sent him this information. THanks!

Answer 5

SQL Server Management Studio...forgot to answer your question!

Answer 6

are you able to post a screen shot of the connection box?

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 7

The User name and Password boxes (in the Connect to Server dialog box) should be greyed out if you have selected Windows Authentication. When using Windows Authentication, the connection will be made using the credentials that Windows already has; the Windows user that logged into the computer. So it doesn't need more user name and password information.

When SQL Server is installed, the setup program asks which Windows users should be added as SQL Server administrators. Adding the user who is installating SQL Server is the standard choice, though not required. If your Windows user was not added as a SQL Server login when SQL Server was installed, then you will not have access until you have a SQL Server login tied to your Windows user name. This can be added by the user who was selected as the administrator during installation of SQL Server. Or, if you are an administrator of the computer, you can go through the steps here Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out (which is a bit more trouble, but it is an unusual situation).

When you run sqlcmd -L, what name is returned? The instance of SQL Server that goes into the Server name box or the Connect to Server dialog box, is either the computer name (e.g. NewComputer ) or a computer name and instance name (e.g. NewComputer\SQLEXPRESS or NewComputer\TestSQL ). I ask this because your second post said my machine name \ my user name . That only makes sense if you installed SQL Server with your user name as the instance name. Hope all this helps.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 8

Rick,

yes, thank you...this is making a lot more sense. First, let me say that when I run sqlcmd -L the return name is my machine name only.

 

When I installed SQL server, I did not specify any usernames at all.  My userid is the administrator on the machine and I have a single user machine, no other userids on the machine. When I read the information in Troubleshooting (thank you!) it seems the easiest for me would be to uninstall and reinstall SQL server as I have no userids or existing databases to recover. I'm just starting with this.

 

Would you agree this would be the simplest answer to this? Or will uninstall get me in more trouble?

Answer 9

HI VAKaren,

Please make sure that you have typed Database name correctly. And provided right credentials.

 

Answer 10

Hello,

The default database is the master database and it is the database which is used to create new users ( Windows or SQL Server ).

You must use the same credentials ( that's to same the same Windows login as this which was used to install your SQL Server ).

Have you checked whether the firewall is not blocking any communication between your SQL Server and the applications trying to connect to this SQL Server (SSMS, Sqlcmd ) ?

Have you checked whether the sqlBrowser service is running ? ( it should be always set automatic and started with the SQL Server Configuration manager )

What is the edition of your SQL Server (Express,Web,Workgroup,Standard,Developper,..) ?If Express , the default name is SQLEXPRESS , so the name of the your SQL Server instance is NameOfYourComputer\SQLEXPRESS or as you are using a locale instance .\SQLEXPRESS.If not Express edition, the default name proposed is an empty string ( you have a default or no-named instance ),you have to use . or the name of your computer as the name of your SQL Server instance.

We are waiting for your feedback to try to help you more efficiently.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 11

I am running a single user machine so the credentials should be the same. When I installed SQL Server (express version) I did not have a password on the machine as it was a brand new purchase and I just had not added a password. Somehow I am thinking this may be part of the problem.

 

When I run sqlcmd -L I see the name of my machine only, no instance listed.

When I start SQL server configuration manager, I can see the SQL server is running and is set to automatic; however, the SQL Server Agent and SQL Server Browser services are listed as stopped. I'm unable to start either server as they are greyed out. When I go into Properties, under user account there is no name but there appears to be a password as it is greyed out. When I try to add a user name and a password I get a message 'Cannot be started, no enabled devices associated with it'.

I'm becoming more convinced I simply installed this wrong. First, I had no Windows password on the machine. Second, during the installation I did not enter a userid or password. I believe SQL cannot therefore authenticate to anything.

I have no databases as this is a new install. Can I simply uninstall and reinstall? The only thing I cannot mess up is my network access as I CAN successfully logon to the SQL service at the college and need that to continue to work as I am teaching in January using that service.

Answer 12

Hello,

SQL Server 2008 R2 does not accept the use of Windows login having an empty password ( same problem than me with the Windows 7 of my son ).Anyway, to have a Windows login with an empty password ( especially if this login has administrator rights ) is an enormeous security break.I hope you will modify that quickly.

For the services :

- the SQL Server Browser service is stopped : that's the default value during the install, but when it is stopped, very few applications can detect installed SQL Server instances : set this service with an automatic start and sqlcmd or SSMS will be able to  fully detect any installed SQL Server instance. Sometimes, it is adviced to stop it for security sake,in your case, i don't think this drastic security rule is a real problem for you.

- the SQL Agent Service exists in the SQL Server Express edition since SQL Server 2008 but it is desactivated. The reason given by Mike Wachal ( formerly belonging to the SQL Server Express Team ) is : if you upgrade an Express edition to a higher edition ( not-free one ), the upgrade has not to create this service only to activate, making the upgrade easier,quicker with less problems.So, it is impossible to start it in an Express Edition ( remark : this "ghost" install was not available with Express 2005, with somtimes problems during the upgrade from Express to Web,Standard,... editions.)

Modify your SQL Browser service to automatic start and retry to connect thru SSMSE or sqlcmd.It should be better, that' to say, you should see your instance.You may also type .\SQLEXPRESS  as name of your instance if you have not modified the default name proposed during the install. The "." character represents a shortcut to the name of the local computer ( easier to type )

In theory, the Windows user used to install a SQL Server instance is sysadmin ( all rights/permissions ) on the system databases.So you should be able to create/modify/drop any "user" databases with no problem ( never modify system databases except if you know the consequences of this modification : it is one of the easiest way to lose your instance )

I don't think that you have to uninstall your instance.On the other hand, i should advice you to install ( the next time ) the SQL Server Express with advanced services : it is the most complete one.It will install automatically SSMSE ( SQL Server Management Studio Express) and the Reporting Services. This last one feature will be configurated minimally but it will avoid you to do an huge download the day you will need to use this Reporting Services ( for the update, it is the same size that for a full install, lost time for a new download )

Try to add a password to your local Windows user.

Don't hesitate to post again for more help or explanations ( as my english is not good, i am sometimes difficult to be understood )

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 13

Papy,

Yes, I did absolutely set a Windows password...that was fixed but it was added after my installation of SQL Express. But it's set now.

When I look at my servers through SQL Server Configuration Manager the SQL Browser is stopped. The actions to start it are grayed out so if not in this screen, where do I go to start the SQL Browser or to set it to automatic?

I added the instance on login screen and I'm no longer getting the error message!! Progress!! Thank you!

Now, how do I get the Browser service to start and how to switch it to automatic?

Answer 14

Hello,

It is simple :you have to use the SQL Server Configuration Manager that you will find in the same way as the SQL Server Management Studio Express

All Programs

Microsoft SQL Server Express

Sql Server Configuration Manager

double-click on it : the program will be launched

In the left-part you should se a treeview of which the 1st node should be labelled as "SQL Server Configuration Manager (local)" ( as i have a french SQL Server , my translation may be incorrect )

double-click on the SQL Server Services node to expand it

all the services related to SQL Server should appeared in the right panel of your screen

find SQL Browser Service ( it is usually the last one of the list of the services )

click on this line and choose the Properties item of the contextual menu.Select the second tabpage (labelled Service ).Go to the 6th line ( Start mode ) if not set to automatic , go to the end of the 2nd column and click on this end.And select automatic .Click on the button Apply.

Click a new time on the SQL Browser Service line select Properties and click on the Start button.The next time you will restart your computer, your SQL Browser Service should start automatically.

As I am using SQL Server Express since 2005, it is always the same problem.I have no problem with my own computer, because i have installed 2008 Developper so this service is automatically started  , but , on the computer of my son, i have only installed 2008 R2 Express so i had to modify the start mode of the service to avoid your problem.

Don't hesitate to post again for more help ( in case of another problem ) or explanations.

Have a nice day

PS : never use the Windows Services Manager to modify the properties of any service related to SQL Server.Only the SQL Server Configuration Manager makes changes in the correct way

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 15

Thank you!!! This is wonderful! I absolutely appreciate all of your time in helping me!

Answer 16

Hello VAKaren,

I have only done my work of moderator, but thanks for your thank you.Too many times , the original poster forgets to mark a reply as the good answer.

We will be always welcome on our forums and beforehand happy new year and enjoy your SQL Server Express

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.