[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server does not allow remote connections - VISTA Ultimate, SQL 2005, VS 2005

Posted on 2007-10-11
20
Medium Priority
?
482 Views
Last Modified: 2013-11-29
When my Web application tries to connect to SQL I receive the following error message:
"An error has occurred while establishing a connection to the server.  
When connecting to SQL Server 2005, this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections."
I have wiped my machine and reinstalled the OS from the manufacturer-supplied recovery disk.  I have installed SQL 2005 (Dev) and VS 2005 (Dev) on a new VISTA Ultimate machine, running 64-bit.  I have tried numerous solutions to this issue from here and from Google.  
1.  I am ruining everything as administrator
2. My SQL Installation is a named installation.
2. In the Configuration Manager, all six of my Services are running, including SQL Server Browser.
3. TCP/IP and Named Pipes are Enabled in all of the available Protocol dialogs.
4.  I have stopped and started all the services and rebooted my machine numerous times.
5. I have checked all the settings in the Surface Area Manager as well - they seem to jive with the Configuration Manager.
6. I am using Windows Authentication, and LocalHost
7. I am at my wits end, and after 15 years, seriously considering a new line of work.
ANY suggestion would be appreciated.
Thank you for your time and attention.
0
Comment
Question by:Eric Christianson
20 Comments
 
LVL 8

Expert Comment

by:Bradley Haynes
ID: 20059835
To double check, do the following:
1. make sure that server and client use the same network protocol (enable TCP/IP).
2. Enable the service SQL Browser from your server.
3. Enable 'Allow remote connections' setting in the server.
4. check your server/workstation firewall.

If all else fails . . . Go back to the drawing board.
It is like when you first put your socks on. After a while it is just there.

Follow these steps to configure SQL Server 2005 to allow remote connections:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.

Note : Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

You can get more details from : http://support.microsoft.com/kb/914277
0
 

Author Comment

by:Eric Christianson
ID: 20059998
Hi -
I've been through (part) of this resolution several times already, but thank you for the step by step instructions.  At least it shows me that I have been doing it right.  Under Remote Connections, I have the radio button labled 'Using both TCP/IP and named pipes' selcted.
Any other ideas?
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20060150
2. My SQL Installation is a named installation.
6. I am using Windows Authentication, and LocalHost

... ehm than you should use 'LOCALHOST\INSTANCENAME' and not just localhost as servername ...

Hope this helps ...
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:Eric Christianson
ID: 20060374
My Server Name is 'ECE'.  My Admin UserName on the machine is 'ECC'.  Where would I enter or expect to see my version of your example string?  (in my case 'localhost\ECE' ??
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20060450
Your web app tries to connect to the SQL Server using a connection string ... in there it might say:

Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<YourDB>;Data Source=localhost\ece

hope this helps ...
0
 

Author Comment

by:Eric Christianson
ID: 20060748
Where are my heart pills when I need them...  I have a different error message (!!!) - this one much more familear.  Yveau, if you could just hang in there just a little longer...
"Login failed for user 'ECE\ECC'  The user is not associated with a tructed SQL Server Connection'
My web.config looks like this right now:
<appSettings>
      <add key="dbServerName" value="localhost\ECE"/>
      <add key="database" value="LMOL"/>
      <add key="dbUserName" value="ECE\ECC"/>
      <add key="dbPassword"/>
Where is SQL do I make a change?
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20060846
Aha, that one is familiar as well :-)

You are mixing up the two authentication modes for SQL Server.
1. There is SQL Server authentication, and for that to work, you need to enter a SQL login/Password in the connection parameters as you are doing.
2. There is Windows integrated security, and for that to work you may NOT enter a login password in the connection parameters (otherwise SQL switches to SQL authentication)

so I guess it will be something like:
<appSettings>
      <add key="dbServerName" value="localhost\ECE"/>
      <add key="database" value="LMOL"/>
      <add key="dbUserName" value="sa"/>
      <add key="dbPassword"/>
or
<appSettings>
      <add key="dbServerName" value="localhost\ECE"/>
      <add key="database" value="LMOL"/>

Hope this helps ...
0
 

Author Comment

by:Eric Christianson
ID: 20060906
Well, 'sa' doesn't work (currently) in my web.config.  If I open up SQL, where do I go to change it from Windows Authentication to SQL Server Authentication?
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20061005
Hang on !!! I wasn't clear enough. My fault !
It's either ONLY windows auth. or BOTH SQL and windows auth.

So you can set it to Mixed mode as they call it, this way:
In SSMS, open a connection to the server. Then in the object explorer, right click the name of the server, select properties. Then go to the security page and click the radio button saying 'SQL Server and Windows Authentication Mode'.

Next to that, using the sa account is REAL bad practice. So please, create another SQL login, and use that one for the application. ... testing the whole package using sa is of course OK ... but don't forget to change it as soon as it works ...

Hope this helps ...

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20061011
... and when you create another login, also create an accompanying user in the correct database ... otherwise you still won't be able to get into the database you need.

0
 

Author Comment

by:Eric Christianson
ID: 20061223
Almost there, I can feel it.  There is still something wrong.  Same error message Login failed for user 'sa'.  My web.config looks like:
      <appSettings>
            <add key="dbServerName" value="localhost\ECE"/>
            <add key="database" value="LMOL"/>
            <add key="dbUserName" value="sa"/>
            <add key="dbPassword"/>
My server properties show 'SQL Server and Windows Authentication' checked.  
I do not have a Server proxy account, even through there is a (disabled) password in the password field.  
In my old machine, which uses a default SQL install (not named), my 'sa' acoount was ONLY a SQL Server Authentication (there was no option for mixed).
What next?
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20061261
We're going to get there ...

You still need to pass the sa password:
      <appSettings>
            <add key="dbServerName" value="localhost\ECE"/>
            <add key="database" value="LMOL"/>
            <add key="dbUserName" value="sa"/>
            <add key="dbPassword" value="secret"/>

If you do not know the password, first reset it using SSMS.

... hope this helps.
0
 

Author Comment

by:Eric Christianson
ID: 20061360
That's the problem.  I don't know where to set the password.  If I click on the Enable Server Proxy Account checkbox, and enter 'sa' in the proxy account field, the pre-loaded password remains intact regardless of what I type or if I stop the server and restart.  Is this where I add a password for 'sa'?
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20061407
Stop, the proxy account is something different.
Check this:
In SSMS, connect to the server.
In the object explorer pane, open the server and open the security folder and open the logins folder.
Right click on 'sa' and select properties.
There you have the password ... enter it twice and you should be fine.

Hope this helps ...
0
 

Author Comment

by:Eric Christianson
ID: 20061510
I have to get my kids - I'll be back in 30 minutes...

Login was  Disabled - I enabled it.
User Mapping (unmapped) - I mapped it to my db.
Now I get Cannot use the special principal 'sa'.  eccent  Error 15405

(weep)  I'll be back
0
 
LVL 18

Accepted Solution

by:
Yveau earned 2000 total points
ID: 20061575
When you use the 'sa' login, that's the only one that does not has to have an according user.
That one is 'dbo' by default in each database.

error doesn't ring a bell ...

have to catch some sleep ... sorry.

I'll get back to you in about 7 hours.
0
 

Author Comment

by:Eric Christianson
ID: 20061712
Thank You, Thank You, Thank You.  I came back and tooled around with a new Login User, had some issues with granting access, etc., but eventually I was able to connect and get to my data.  I probably do not have an optimal set-up, but it works, and I can move on.
I am a decent programmer, but its been a while since I had to futz with installing SQL, and doing so in a new environment like VISTA is, in my opinion, ridiculously complex.  Five weeks ago I bought a new machine to speed up my dev experience.  I have contacted Experts Exchange on three different issues including this one, just to get SQL installed and working.  Today I can say I am finally moving forward.  There has to be a better way.
Thank you for your time and attention, Yveau.  You really were a good source of useful information
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20063092
Glad I could be of any help and thank you for the grade !
0
 

Expert Comment

by:yitbos33
ID: 20087741
Thank you for this thread. I spent hours trying to get connected, and it finally dawned on me after reading this thread that I needed to put the instance name after the hostname in order to access the sql server remotely...Much obliged
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20088955
:-) Cool ... now we're even helping out without doing anything !
Happy SQLing !
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows 10 Creator Update has just been released and I have it working very well on my laptop. Read below for issues, fixes and ideas.
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question