Connecting to SQL Server 2005 on Vista and Longhorn

Connecting to SQL Server 2005 on Vista and Longhorn

Rate This
  • Comments 33

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 BUILTIN\Administrators 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 http://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 'domain\user'

or

EXEC sp_addsrvrolemember 'domain\user', '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

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
  • 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?

  • 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 Kostov

  • 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,

    Tim

  • Could you please check that the very same connection (<server ip>,<port>) succeeds from the XP machine?

    Could you also please attach a portion of the SQL Server errorlog (from <installed dir>\MSSQL\LOG, most likely \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG)? Feel free to mask any sensitive information you may have in the log; actually, I'm mostly concerned about the line that gives information about the port number, something like

    Server is listening on ['any' <ipv4> <Port Number>] or [<ipaddress> <ipv4> <Port Number>].

    Lastly, could you please give some information about the app you're using to connect to SQL Express? Is it the same on XP and Vista? Does it have the same version?

    If nothing helps, you probably have some restrictions on your Vista machine that are not SQL Server related. The error message you see usually indicates that either the port number is incorrect or the target machine is not listening on it, so if exactly the same thing works from your XP box, neither of these would be possibility. Please also see the blog at http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx, it discusses the meaning and troubleshooting of this error message.

    Thank you

    Stoyko Kostov

  • When I attempt to connect to SQL Server 2005 Express on Vista, and doing so with TCPIP and a .NET 3.0 application, I get an 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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

    I have Named Pipes Enabled.  I have the server instance set to allow remote connections.  I have the user name defined in Security.  I just keep getting refused on my Vista box.

    We have found that we get this error when the login is not allowed to sign on to Sql Server.  However the userID is allowed and successfully connects to servers running under Windows Server 2003.  The error logs don't show anything to help trace the problem.

  • I have the same problem as Tim above running a vista client.

    I have been stuck on this for over a week.

    I have uninstalled and reinstalled multiple times and turned of the client firewall and virus software.  The server is fine I can connect from an XP box with the same login.

    I have SQL 2005 SP2 installed on my client machine.  The server is 2005 and has SP1.  Remote conns allowed port 1433 open etc

    I am going to have to spend next weekend installing XP if I cant solve this.

    "The list of databases for the specified SQL Server could not be retrieved:

    A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

    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: TCP Provider, error:0 - No connection could be made because the target machine actively refused it.)

    "

  • You are not alone.  I have this same problem.

  • 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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe as an Exception Program in Firewall

    Enable SQL Browser Service (if not already specified during install)

    Specify SQL Server Browser C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.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.

  • 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.

  • I have de the same problem, please somebody can help me please

  • 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.

  • yo tengo el mismo problema!

    Tengo Sql Server 2005 corriendo en Windows Server 2003.

    Los Clientes XP conectan sin problema a una base de datos de mi servidor, pero los clienetes vista no pueden.

    He rebisaado Firewall cliente y Servidor, he hecho multiples paso que he leido en foros y sin embargo no he encontrado la solucion. Si la encuentro lo estaré escribiendo en este post.

    Si alguien puede sugerirme algo! gracias! gerencia@apoloweb.com

  • 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.

  • !!!!! 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.

  • 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.

Page 1 of 3 (33 items) 123