Connecting to SQL Server 2005 on Vista and Longhorn
Some customers have experienced problems connecting to SQL Server 2005 on Vista and Longhorn. Even though they are trying to connect using an account that is an administrator on the box, they get a "Login failed for user" error message with a state of 11.
The reason for this is that Windows Vista includes a new feature, User Account Control (UAC), which helps administrators manage their use of elevated privileges, and Windows Vista users that are members of BUILTINAdministrators are not automatically added to the sysadmin fixed server role when they connect to SQL Server.
SQL Server 2005 SP2 provides an option during the setup process allows you to automatically add the user who is running setup into the SysAdmin Fixed Server Role. This option is turned off by default. To enable it, users can use the Setup UI by checking the checkbox in the Configuration Options page of the Setup UI with the caption 'Add user to the SQL Server Administrator role'. Alternatively, this can be done using the setup command line by specifying the ADDUSERASADMIN=1 option in order to add a login for the user running setup. To not add a login for that user, use ADDUSERASADMIN=0 (the default behavior).
This is discussed in detail at https://blogs.msdn.com/sqlexpress/archive/2006/11/15/sql-express-sp2-and-windows-vista-uac.aspx.
Users can always connect to SQL Server using elevated privileges (run as Administrator), or connect as sa (if Mixed authentication mode was selected during install). Once connected, any user can be granted login privileges or added to the sysadmin fixed server role by executing
EXEC sp_grantlogin 'domainuser'
or
EXEC sp_addsrvrolemember 'domainuser', 'sysadmin'
respectively.
Once this is done, users who have been granted login privileges or added to the sysadmin fixed server role no longer need to run as administrator in order to connect to SQL Server.
Stoyko Kostov, SQL Server Protocols
Comments
Anonymous
June 18, 2007
I'm having an issue connecting (using the TCP/IP provider) from our Vista laptops to SQL server express (located on another computer running Windows SBS 2003). The Vista laptops are joind to the same domain as the server. There is no problem when connecting from a Windows XP computer, joined to the same domain. I've disabled UAC and the firewall on the Vista computers. When attempting to connect as <server><instance> the error is "error 26: Error locating server/instance specified". When attempting to connect as <server><instance>,<port> ther error is: "error 0: No connection could be made because the target maching actively refused it". Any ideas as to what to try next?Anonymous
June 18, 2007
My suggestion for you is to ping your Server machine from both the XP and Vista computers. Compare the results. Hopefully they match. Then try your connection from the Vista machine using this IP address (the one from the XP, if they are different): <IP address>,<port>. Make sure you specify the correct port from the SQL Server errorlog. You don't need to specify the instance name if you explicitly specify the port. You also mention that you have UAC disabled, but I recommend you still try your application under elevated mode (run as administrator). Let me know what you observe; this will give me a better clue to what the problem may be. Thank you Stoyko KostovAnonymous
June 19, 2007
Hello Stoyko, Thanks for your suggestions. After pinging the server from both computers I get the same ip address. After attempting to connect to the server using <server ip>,<port> while running as an administrator I get "error 0: No connection could be made because the target machine actively refused it." Thanks for your assistance, TimAnonymous
June 19, 2007
The comment has been removedAnonymous
June 29, 2007
The comment has been removedAnonymous
August 11, 2007
The comment has been removedAnonymous
September 10, 2007
You are not alone. I have this same problem.Anonymous
September 11, 2007
I am having a problem with SQL Server when I try to connect from Visual Studio. I have done the following (along with multiple re-installs and in numerous combinations): While installing, uncheck the "Hide Advanced options" Specify mixed authentication and provide the password for sa Select the Add Administrator to sysadmin Role checkbox while installing. After installation, Enable TCP/IP and named pipelines through Surface Area COnfiguration or via My Computer> Manage > Services and Applications > SQL Server Configuration Manager > SQL Server 2005 Network Configuration > Protocols for SQLEXPRESS >>> Enable TCP here. Specify SQL Server executable C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlservr.exe as an Exception Program in Firewall Enable SQL Browser Service (if not already specified during install) Specify SQL Server Browser C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe as an Exception Program in Firewall. I guess thats it. My SQL server works but i do not know why i keep getting this 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. (provider:SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified).
On the other hand, I can log in into the server with both windows authentication and SQL Server Authentication via the SQL Server Management Studio. So why is it not possible from Visual studio? Any ideas - please help.
Anonymous
September 11, 2007
In addition to above, (I just missed out mentioning, but did not miss out in configuration), I have also enabled the remote connections by selecting both TCP and managed pipelines from the SQL Surface Area Configuration. I had earlier thought this was the problem, but the error still comes up saying that MAY BE the failure is caused due to the fact that SQL Server does not allow remote connections!!!!. Please help.Anonymous
October 29, 2007
I have de the same problem, please somebody can help me pleaseAnonymous
November 05, 2007
I had the same problems connecting to SQL Developer Edition, but had also one sucess: I installed SQL Express Edition with a default name and could connect this instance from remote. A named instance of Express Edition caused again connection problems, so only default name (MSSQLSERVER) was sucessfull. I couldn't find the differences between instance with a default name and named instance.Anonymous
November 22, 2007
The comment has been removedAnonymous
December 07, 2007
Señores logre solucionar mi problema!! Todo se devia por permisos o provilegios en el Windows Server 2003. Para los que estna teniendo el mismo inconveniente rebise los permisos en el servidor.Anonymous
December 07, 2007
!!!!! HECHO PROBLEMA SOLUCIONADO!!!! Señores logre solucionar mi problema!! Todo se debia por permisos o provilegios en el Windows Server 2003. Para los que estan teniendo el mismo inconveniente revise los permisos en el servidor.Anonymous
December 07, 2007
For English readers, a summary of totigo problem: He had a problem, his XP clients connected correctly but Vista clients weren't. The solution was to correct server permissions.Anonymous
December 14, 2007
Yeah!! windows server permissions is the solution!!Anonymous
December 28, 2007
I had the same problem with tcpip, connecting to <hostname>SQLEXPRESS only worked on named pipes, finally i just connected to <hostname> over TCP/IP and it worked. I don't know about named instances and whether they will work (vista 32bit)Anonymous
January 07, 2008
NOTE: this STOPPED working later, i don't know whyAnonymous
February 05, 2008
One of the nice things about moving from VS2005 to VS2008 is that you no longer need to run as adminAnonymous
February 05, 2008
One of the nice things about moving from VS2005 to VS2008 is that you no longer need to run as adminAnonymous
May 28, 2008
I've got SQLExpressSP2 on a Vista machine, with a named instance. You have to enable sqlbrowser, and you have to allow sqlbrowser.exe and sqlserv.exe through the firewall. This allows me to use SQL Server Studio on another machine to connect to that instance.Anonymous
July 25, 2008
I was getting this same problem on Windows XP with SP3... does SP3 introduce anything that might cause this same problem on XP? The solution I used was to simply add my windows account as a user in SQL... even though the BUILTINAdministrators group was listed as sysadmin.Anonymous
July 30, 2008
Use sql native client for connecting to sql server 2005 in vista..Anonymous
August 04, 2008
I also found the following message if any one found solution about this pls tell me the solution at nikhil338389@gmail.com 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. (provider:SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified).Anonymous
August 19, 2008
I'm having this same issue using a client application, written in C#, running on a Vista Biz PC connecting to a SQL 2005 Standard Edition with the latest SP installed. XP clients work perfectly, but not the Vista clients. Firewalls are turned off, permissions are okay, named pipes and tcp/ip ok, standard ports (1433, 1434) open, tada tada tada. If anyone truly solves this, please let me know. I am about to toss Vista out the window.Anonymous
August 19, 2008
Sorry, forgot to leave my email.Anonymous
August 27, 2008
what is the final resoultion for this connectivity issue. regards, chandra pathivada.Anonymous
September 18, 2008
Hi, I have installed SQL Server 2005 on a Vista Business. I am able to connect to SQL Server instance on Vista machine from Management Studio on other machine (Running on Xp). But I am not able to connect to Analysis Services default instance on Vista Business machine from Management Studio(Client) on other machine (Running on Xp). I did all settings with Vista firewall exceptions, SQL Surface area configuration, Security, everything. Please tell me do I need to modify something else on ‘Vista Business’. Thank you.Anonymous
October 18, 2008
The comment has been removedAnonymous
October 23, 2008
Yea! Which Permission????? You kinda left me hanging here!!! I have been researching this problem for hours now and this site seems to be the one that is having exactly the same problem as me. Vista machine with SQL server, XP machine fine, additional Vista machine not able to connect. Please give a little more details!!!!Anonymous
November 01, 2008
I found that SQL Server Browser windows service, after MSDE (SQL Serv Expr) is instaled, sometimes is disabled. I spend days looking for regular options to enable network loging, network services etc.etc.and everything was fine, but network loging simply did not worked. When I enabled and started this service miracle happend... Before this afcourse you have to check with Configuration Manager is TCP/IP Clieant protocol enabled, with Management Studio is Sql Server authentication mode selected, and those standard things. After those checks everything worked for me at Vista and XP. Regards.Anonymous
December 17, 2008
usefull article, thanks http://developerskb.blogspot.comAnonymous
April 12, 2010
am new to sql database,after installing the required software i tried to conect to the database and i got the following error TITLE: Connect to Database Engine
Cannot connect to ADEJ-PCSQLEXPRESS.
ADDITIONAL INFORMATION: 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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
BUTTONS: OK
pls can you help me out my email is adej20032000@yahoo.com