Vista SQL 2005 SP2 client to Windows XP SQL 2005 (non-sp2) workstation.

I'm trying to use SQL Management Studio to connect from a Vista SQL 2005 client w/ SP2 over to a Windows XP workstation running SQL server developer edition (non-sp2) using windows authentication.

I'm already preauthenticated by network shares to the xp workstation. This worked just fine in windows xp but stop working in vista.

After searching around, i found out that vista handles trusted authentication differently than windows xp due to changes in the SSPI. Logging in as sql authentication works fine. Logging in as windows authentication does not.

UPDATE: I found out that two workstations running vista and sql 2005 sp2 cannot connect to each other using windows trusted authentication!!!

What the heck

UPDATE: I found out that my IIS 7.0 using ASP.NET and connecting to SQL 2005 (non sp2) on a Windows 2003 server (SP1) WILL connect to the database using trusted authentication... apparently IIS can access the credentials and pass them to the sql 2005 box just fine. My Sql 2K5 SP2 tools cannot. I'm forced to use SQL authentication to the box.

Same thing applies with a Windows XP SP2 box running SQL 2005 (non sp2).

Here is my sql server error log off of the windows xp workstation:

2007-04-26 15:41:32.34 Logon Error: 17806, Severity: 20, State: 2.
2007-04-26 15:41:32.34 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.254.100]
2007-04-26 15:41:32.34 Logon Error: 18452, Severity: 14, State: 1.
2007-04-26 15:41:32.34 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.254.100]

How can i Fix this issue

[1] Client side:

What is the connection string in you app or DSN
N/A
If client fails to connect, what is the client error messages

Is the client remote or local to the SQL server machine
Remote

Can you ping your server
YES

Can you telnet to your SQL Server
YES

What is your client database provider
SQL Management Studio

Is your client computer in the same domain as the Server computer
WorkGroup

What protocol the client enabled
Shared Memory & TCPIP

Do you have aliases configured that match the server name portion of your connection string
NO

Do you select force encryption on server and/or client
NO

[2] Server side:

What is the MS SQL version
SQL Server 2005

What is the SKU of MS SQL
Developer

What is the SQL Server Protocol enabled
Shared Memory & TCPIP

Does the server start successfully
YES

If SQL Server is a named instance, is the SQL browser enabled
N/A

What is the account that the SQL Server is running under
Network Service

Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider
YES

Do you make firewall exception for SQL Browser UDP port 1434
N/A

[3] Platform:

What is the OS version
Client - Windows Vista Final w/ SQL Server 2005 SP2 & Network Client SP2

Server - Windows XP Professional SP2 w/ SQL 2005 Server Developer Edition

Do you have third party antivirus, anti-spareware software installed
None

[4] Misc:

If you have certificate configuration issue: Please use ˇ°certutil.exe ¨Cv ¨Cstore myˇ± to dump certificate specific info and post it in your question.
N/A

Tips:

1. Find SQL Server Errorlog: Default to C:\Program Files\Microsoft SQL Server\MSSQL.#\MSSQL\LOG

2007-04-26 15:41:32.34 Logon Error: 17806, Severity: 20, State: 2.
2007-04-26 15:41:32.34 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.254.100]
2007-04-26 15:41:32.34 Logon Error: 18452, Severity: 14, State: 1.
2007-04-26 15:41:32.34 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.254.100]

[1] Client side:

What is the connection string in you app or DSN
N/A
If client fails to connect, what is the client error messages

Is the client remote or local to the SQL server machine
Remote

Can you ping your server
YES

Can you telnet to your SQL Server
YES

What is your client database provider
SQL Management Studio

Is your client computer in the same domain as the Server computer
WorkGroup

What protocol the client enabled
Shared Memory & TCPIP

Do you have aliases configured that match the server name portion of your connection string
NO

Do you select force encryption on server and/or client
NO

[2] Server side:

What is the MS SQL version
SQL Server 2005

What is the SKU of MS SQL
Developer

What is the SQL Server Protocol enabled
Shared Memory & TCPIP

Does the server start successfully
YES

If SQL Server is a named instance, is the SQL browser enabled
N/A

What is the account that the SQL Server is running under
Network Service

Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider
YES

Do you make firewall exception for SQL Browser UDP port 1434
N/A

[3] Platform:

What is the OS version
Client - Windows Vista Final w/ SQL Server 2005 SP2 & Network Client SP2

Server - Windows XP Professional SP2 w/ SQL 2005 Server Developer Edition

Do you have third party antivirus, anti-spareware software installed
None

[4] Misc:

If you have certificate configuration issue: Please use ˇ°certutil.exe ¨Cv ¨Cstore myˇ± to dump certificate specific info and post it in your question.
N/A

Tips:

1. Find SQL Server Errorlog: Default to C:\Program Files\Microsoft SQL Server\MSSQL.#\MSSQL\LOG

2007-04-26 15:41:32.34 Logon Error: 17806, Severity: 20, State: 2.
2007-04-26 15:41:32.34 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.254.100]
2007-04-26 15:41:32.34 Logon Error: 18452, Severity: 14, State: 1.
2007-04-26 15:41:32.34 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.254.100]

Apparently it's not that simple as I thought at first.

Disableing the firewall will allow you to connect.

Add Managment Studio as an exception seemed to work for a short while, but maybe some connections were still open from when the firewall was disabled.

It surely has something to do with the firewall...

First of all I am unable to connect from Management Studio. I have SQL Server 2005 and SP2 installed on Vista. I am trying to connect to a SQL Server 2005 on our network using windows authentication. It used to work fine from XP. I am on the same network but not on same domain, so I am using work group. I can access the Server from windows explorer without any problem and I have saved it in my network passwords.

When I was trying to connect first time, it was showing me error that Remote connection is not enabled on SQL Server 2005 but as I add the Management Studio in firewall exception rules, then error was changed. Now, when i try to connect to that remote SQL server 2005, it shows me error that " Login Failed for user '' ".

From my ASP.Net application when i used the following connection string, it also fails:

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<MyDatabaseName>;Data Source=<MyServerName>

I searched it on net but only what I found is that it is a problem but no one suggest any solution for these two problems. Your help will be really appreciated.

No, my Vista machine is not in the domain. I am connecting to the domain as a workgroup.

Yeah, the SQL server is a named instance on the Server.

My client machine is Vista and Server is Windows 2003 Server which is hosting SQL Server 2005. On my client vista machine I also have SQL Server 2005 but I am not running it but I am trying to connect to 2003 Server, hosint SQL Server 2005 using my vista machine SQL Management Studio. ( on vista I have installed SQL Server 2005 sp 2 as well.)

The solution which told me, is it the only option Previously I had windows xp on my client machine and it was also on workgroup but I didn't do this same user account thingy that time and it worked fine. Is it something Vista specific thing and that is the only option in Vista now to access some Remote SQL Server which is not on the same domain

OK. Let me explain it:

We have a domain controller and all domain users are created. My domain user is abc@myDomain.com. My client vista PC is not part of that domain, it is a on a workgroup (All settings which I am telling here were applicable on my previous windows xp machine too and it was able to connect without problem). So, when I need to access any PC or server at work ( to get some file or print ), I simply give my domain user id and password and connect it.

Same was true for my Windows 2003 Server which hosts the SQL Server 2005. First time I access that server from windows explorer, it asks for User Name and password. I supplied my credentials and it stores it in the network user credentials of windows, so next time when I access the same server it doesn't asks me for userid and password. This thing then helps in accessing SQL Server 2005, using windows authentication, in past.

In past, when I use XP with same above situation, it access SQL Server without any problem , by using those stored network credentials. But vista client is not able to do so, even though the user is added in network users list of windows.

For a work around of this vista issue, I created a SQL user and then I tried to access the Remote SQL Server from my vista machine with those SQL server credentials. It worked fine but ASP.Net was now unable to access that Remote SQL Server. Here is the connection string:

I tried to create a "UDL" file to build connection string, but the UDL file on vista was also unable to connect to remote SQL server, even with SQL user name and password.

I tried the "np" solution but it didn't work. We are accessing from asp.net using SQLClient and network protocol is enabled on the server. Here is the ASP.Net errror which I get after putting np: in connection string:

Exception Details: ConnectionFailedException: The connection source 'ApplicationSource' could not be established. Check your connection definition, network and database server.
Connection Details: {ConnectionDefinition: Name=MyServer, Provider=SqlServer.SqlServerProvider, Platform=SqlServer}
Internal Exception: System.Data.SqlClient.SqlException
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)

It was working fine from windows XP, I mean the remote connections are allowed on SQL Server and it is still working fine on other XP machines.

Update:

Further I found out that when I try to use Management Studio to connect with my SQL Server 2005, and in the connection dialog I clicked "Options" and in the "Connection Properties" I changed the Network Protocol from Default to Nmaed pipes and try to connect with SQL user name and password and it failed. Then I change connection Properties to TCP/IP and it connected.

I tried to export a database from my Remote SQL Server 2005, the export wizard started and I tried SQL Native client, OLEDB Provider for SQL Server and provide my my SQL User Name and Password but it kept showing me the same message which I put above that SQL Server does not allow remote connection in default settings. This was a little annoying because I am connected to that server in Management Studio but it's export feature is not recoganizing it.