SQL Server Windows Auth for ETL called by CreateProcessAsUser

lloydsantos


Hi,

We are having problems in our project. We have ETL packages we call from our web app that we want to run and the user is the logged in one in the web.

We use Windows Authentication in our web app and we used the CreateProcessAsUser to impersonate the user. <identity impersonate="true"/> is also in our web.config.

We were able to call the dtexec successfully (with the user for the process as the logged in the web -- thus impersonation was successful.)

But, a problem arises when we use Windows Authentication for the connection of the ETL to the db (SQL Server). When we run the ETL on our local machine using dtexec/dtexecui, it works fine.

But when we ran it in our server, it fails. According to the event logs, it can't connect to the db server even though impersonation is successful.

Btw, the client, web server, db server are three different machines.

Any help is appreciated. Thanks!

JayH


This is the NTLM "double-hop" authentication problem. The impersonated credentials cannot be sent to a third machine unless the Kerberos protocol is used.

http://blogs.msdn.com/nunos/archive/2004/03/12/88468.aspx
http://blogs.msdn.com/knowledgecast/archive/2007/01/31/the-double-hop-problem.aspx

lloydsantos

Hi,

I was already able to solve the double-hop issue for this. Though, I have problems.

First, the ETL (or dtexec) does not run when the impersonated user is not part of the locla Administrators group and having the local privilege "Replace a Process Level Token".

Having those rights assigned, the ETL runs but again fails because it can not connect properly. Based on my logs, the impersonated user is the "operator" or caller of the dtexec but when it tries to connect to SQL server, it does not use the impersonated identity but it uses "NT AUTHORITY/Anonymous Logon". Because of this the ETL call fails.

I've researched some, can "cloaking" (COM) help here

Thanks! Any help is appreciated!

Michael Entin - MSFT

How did you solve the double-hop issue Your statement that DTEXEC connects to SQL Server as Anonymous contradict the statement that it has been solved.

SSIS does not do any impersonation or token manipulation internally, thus it will use the process token to connect to SQL Server. Since it connects as Anonymous, something is wrong with this token (that you've created) - double-hop is most common problem.

lloydsantos

Hi,

We've set up Kerberos Authentication for this. We used KB/articles regarding Kerberos/constrained delegation. I said that we solved the double-hop issue because I am now able to login the Web App and the DB server using my identity.

Also, when running CreateProcessAsUser, it says on the trace logs that the "creator" of the process was my Identity. Also, in the Windows Events Log, in the information/warning/error messages for the ETL, it says that the "Operator" is the impersonated identity.

Thanks.

Regards,
Lloyd

lloydsantos

Here's the summary of the steps we've taken to set up Kerberos.

I. Force Kerberos authentication to SQL Server
(http://support.microsoft.com/kb/909801)
1. As domain administrator, set the following SPNs
- setspn ¨Ca MSSQLSvc/dbhostname:1433 dbaccount
- setspn ¨Ca MSSQLSvc/dbFQDN:1433 dbaccount
where
dbhostname is the hostname of the SQL server
dbFQDN is the fully qualified domain name of the SQL server
dbaccount is the domain account for startup of the SQL services
2. Remote connect to the SQL Server via SQL management console and execute ¡°select auth_scheme from sys.dm_exec_connections where session_id=@@spid¡± to check Kerberos is used for authentication

II. Force Kerberos authentication to IIS Server
(http://msdn2.microsoft.com/en-us/library/ms998297.aspx) (http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/kerbnlb.mspx)
1. at IIS Server, run ¡°aspnet_regiis -ga iisaccount¡±
where iisaccount is the domain account for startup of the IIS services
2. Add iisaccount to the local ¡°IIS_WPG¡± group
3. Ensure iisaccount has the following local privileges
-Act as part of the operating system
-Impersonate a client after authentication (granted via IIS_WPG)
4. Change the Application Pool Identity to iiaccount
5. Change the web ¡°Authentication and access control¡± to Integrated Windows Authentication
6. run ¡°adsutil set w3svc /ntauthenticationproviders negotiate, NTLM¡± to force precedence of Kerberos over NTLM
7. As domain administrator, set the following SPNs
- setspn ¨Ca HTTP/iishostname iisaccount
- setspn ¨Ca HTTP/iisFQDN iisaccount
8. Check Security folder under Event Log for Authentication Package=Kerberos for access to the web site.

III. Enable constrained delegation between SQL and IIS
(http://msdn2.microsoft.com/en-us/library/ms998355.aspx)
(http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx)
1. As domain administrator, enable ¡°Trust Computer for Delegation¡± to ¡°MSSQLSvc/dbhostname:1433 dbaccount¡± for iisaccount & iishostname
(CIFS-common Internet File System service is required for access to file share)
2. As domain administrator, ensure all user accounts have ¡°Account is sensitive and cannot be delegated¡± cleared
3. Define connection string to the SQL DB at the ASP.NET configuration
4. Use Windows authentication at the ASP.NET configuration and ensure impersonate="true" was set at web.config
5. Test connect DB via IE and a Kerberos connection from iishostname should be found by running ¡°select * from sys.dm_exec_connections¡± via SQL management studio

lloydsantos

Also, I am able to only run DTEXEC when the user is the a member of the local admin group and has the "replace a process level token" privilege on the server. But during running, it encounters errors stated above.

But when not having those rights, the DTEXEC won't really run.

Any help would be appreciated. Thanks!

lloydsantos

I've tried setting bInheritHandles in SECURITY_ATTRIBUTES used in CreateProcessAsUser. But, I think it's not related to it. I'm already lost with this.