Client Windows Vista e successivi e connessione verso SQL Server in workgroup con autenticazione integrata di windows

Si consideri il seguente scenario: macchina Windows Vista (o successivi) in dominio o workgroup che deve connettersi ad un’istanza di SQL Server su una macchina in workgroup con autenticazione Windows.

Per utilizzare l’autenticazione Windows in questo scenario sul client è possibile memorizzare una password di rete (per informazioni si veda Gestione delle password di rete), ovviamente in questo caso verrà utilizzata l’autenticazione NTLM (per ulteriori informazioni su NTLM si veda Autenticazione NTLM alcune note).

A partire da Windows Vista però l’autenticazione Windows verso una SQL Server in Workgroup non funziona e restituisce l’errore “Impossibile eseguire l’accesso per l’utente ”. L’utente non è associato a una connessione SQL Server trusted.” (“Login failed for user ”. The user is not associated with a trusted SQL Server connection.”)

Il problema invece non si presenta nelle seguenti situazioni:

• Se il client è un Windows XP
• Se il client e la macchina che ospita SQL Server sono entrambe in dominio il problema non si presenta, ma in questo caso l’utilizzo dell’Autenticazione Windows comporta l’utilizzo di Kerberos.

Personalmente ho riscontrato il problema in uno scenario con Windows 7 come client in dominio che doveva connettersi ad una istanza SQL Server 2005 SP4 installata su di un Windows Server 2003 R2 SP2 in Workgroup.

Leggendo svariati post e interventi sui forum sembra che a partire da Vista il comportamento delle password di rete (Stored User Names and Passwords) sia stato modificato:

• SQL Server 2005 tools can’t use Windows Authentication with credentials saved in “Manage network passwords
• SQL connecting using Windows Authentication in a Workgroup
• Making SQL Server Management Server work on Windows Vista

Per risolvere il problema ho visto suggerire tre workaround, che per la verità non mi hanno soddisfatto molto, che riporto a titolo di informazione:

Workaround 1:

Creare sul computer su cui è in esecuzione l’istanza di SQL Server un account utente locale con lo stesso nome utente e la stessa password dell’account utilizzato sul client e configurare poi SQL Server per concedere l’accesso a questo utente sui DB necessari.

A riguardo si veda Understanding Kerberos and NTLM authentication in SQL Server Connections:

[2] “Login Failed for user ‘ ‘, the user is not associated with a trusted SQL Server connection”.

In this scenario, client may make tcp connetion, plus, running under local admin or non-admin machine account, no matter SPN is registered or not, the client credential is obviously not recognized by SQL Server.

The workaround here is:

Create the same account as the one on the client machine with same password on the target SQL Server machine, and grant appropriate permission to the account.

Let’s explain in more detail:

When you create the same NT account (let’s call it usr1) on both
workstations, you essentially connect and impersonate the local account of
the connecting station. I.e when you connect from station1 to station2,
you’re being authenticated via the station2’s account. So, if you set the
startup account for SQL Server (let’s assume it’s running on station2) to be
station2’s usr1, when you connect to SQL from station1 with station1’s usr1
login, SQL will authenticate you as station2’s usr1.

Now, within SQL, you can definitely access station1’s resources. Though, how
much access will depend on station1’s usr1 permission.

So far, SQL only deal with an user who is part of the sysadmin role within
SQL Server. To allow other users (non-sysamdin) access to network resources,
you will have to set the proxy account. Take a look at the article for
additional info. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp (xp_sqlagent_proxy_account)

Workaround 2:

Utilizzare l’autenticazione di SQL Server al posto dell’autenticazione integrata di Windows, questa può essere in effetti una soluzione in quanto gli scenari Workgroup/Domain to Workgroup sono in effetti spesso non sono così critici dal punto di vista della sicurezza, ma ovviamente non sempre è così.

Workaround 3:

Eseguire l’applicazione che deve connettersi a SQL Server nel contesto dell’account utente di cui si sono salvate le credenziali nel client, ma anche questa soluzione è poco pratica.

C:\Windows\System32\runas.exe /netonly /user:SqlSrv\SqlUser “Path\App.exe”

Causa del problema:

La causa di questo diverso comportamento delle password di rete (Stored User Names and Passwords) in Windows Vista e successivi la si trova nell’articolo 954545 della KB Microsoft (You cannot use user names and passwords that are stored on a Windows Vista-based or Windows Server 2008-based client computer to connect to a server application that is hosted in a domain):

– On a Windows Vista-based or Windows Server 2008-based computer, you are running a client application that must connect to a server application. For example, you are running a Microsoft SQL Server client, a Microsoft Internet Security and Acceleration (ISA) Server client, or a Microsoft Office Excel client.

– The computer that hosts the client application is in a workgroup or in a different domain from the domain that hosts the server application.

– On the client computer, you have stored the domain credentials in Credential Manager.

In this case, the client application that has to obtain domain credentials from Credential Manager cannot connect to the server application in the domain.

This issue does not occur on a Windows XP Service Pack 2 (SP2)-based computer

When a Windows Vista-based or Windows Server 2008-based client application tries to authenticate credentials, it looks for the target domain name together with a port number. The credential should be stored in the following format:

Computer_Name.Domain_Name:Port_Number

For example, if you run a SQL Server client, you must store the following information:

Computer_Name.Domain_Name.com:1433

However, there is no provision to add “:Port_Number” in Credential Manager.

Quindi tornando al nostro scenario il problema si risolve creando una password di rete anziché per il nome NetBIOS del computer in Workgroup per NomeNetBIOS:1433 (dove 1433 è la porta TCP in cui per default è in ascolto l’istanza di SQL Server).

Sempre nella KB954545 si parla di una hotfix disponibile per Windows Vista with Service Pack 1 e
Windows Server 2008, mentre per Windows 7 e Windows 2008 R2 pare non siano state rilasciate hotfix e da mie prove effettuate Windows 7 SP1 e Windows 2008 R2 SP1 presentano entrambi il problema.

Per avere informazioni su come avvengono le connessioni verso SQL Server (per esempio se viene utilizzato Kerberos, NTLM o autenticazione SQL) è possibile utilizzare la seguente frase TSQL:

Select * From sys.dm_exec_connections

Per ulteriori informazioni su problemi legati alla connessione a Sql Server si vedano:

• SQL Server Windows Authentication (MIX Mode Authenticatio)
• Understanding the error message: “Login failed for user ”. The user is not associated with a trusted SQL Server connection.”
• Client, service, and program incompatibilities that may occur when you modify security settings and user rights assignments