Feedback on: Flynet Designer for Web Services - User Impersonation and DelegationUser_Impersonation_and_DelegatUser Impersonation and Delegation /Dear Support Staff,
User Impersonation and Delegation
Instructions for setting up User Impersonation and Delegation
The following steps are necessary for setting up the server environment for User Impersonation. This set-up allows web service database access on a ‘per-user’ basis. Once this is set up correctly, this means that web service calls originating from each user will be presented each user’s authentication credentials to the database, and this behaviour can be used to filter the data that is visible to each user.
Assumptions made for this document: This gives detailed setup instructions for a system with the servers running Windows Server 2003, and the web server running IIS 6.0, and the database as SQL Server 2005. If you have a different environment configuration, the principles shown in this document should still apply, however some of the details may vary on your system.
What you will need: you will require administrator authority to log on to the Domain Server, Web Server and SQL Server, and sufficient administrator privileges to create new domain users.
1.
Create a domain user account to run the SQL Server service. (e.g. s-SQLServer)
a.
In “Active Directory Users and Computers” on the Domain Server, create a new user account, with the login name s-SQLServer.
b.
Set the password to “Never Expires”, since this is an account for running services.
2.
On the SQL Server machine, use SQL Server Configuration manager to change the log on account for the SQL Server service.
a.
Go to Start à All Programs à Microsoft SQL Server 2005 à Configuration Tools à SQL Server Configuration Manager
b.
In the left hand pane of the configuration manager console, double click on SQL Server 2005 Services.
c.
Now in the right pane, locate SQL Server (MSSQLSERVER) entry and double click on it.
d.
The properties window opens. On the Log On tab, Press the radio button for “Log On As: This Account”.
e.
In the Account Name box, enter the domain name\user name of your s-SQLServer account, or browse to it in the user name list by pressing the browse button. E.g. JimTestDomain\s-SQLServer.
f.
Enter the password for the account in the two boxes provided, and then press apply.
3.
Create a domain user account to run the Application Pool for the web services (e.g. s-AppPool)
a.
In Active Directory on the Domain Server, create a new user account, with the login name s-AppPool.
b.
Set the password to “Never Expires”, since this is a service account.
4.
On a server within this domain, which has the Windows 2003 support tools installed on it, Create the Service Principal Names (SPN’s) for the two domain accounts that you have created. This identifies to the domain that these accounts run services on servers within the domain that will be allowed to pass delegated user credentials between them.
a.
In the command line window (StartàRunà”cmd”) type “cd:\program files\support tools” (If support tools are not installed, you can install them from the Windows Server 2003 installation disk).
b.
At the command prompt, type “setspn -a http/<Web Server FQDN> s-AppPool” (where <Web Server FQDN> is the fully qualified domain name of your web server, e.g. “setspn –a http/ Jim-Web-Server.JimTestDomain.flynetviewer.com s-AppPool”
c.
At the command prompt, type: “setspn -a MSSQLSvc/<SQL Server FQDN>:<port> s-SQLServer” (where <SQL Server FQDN> is the fully qualified domain name of your SQL Server, e.g. Jim-SQL-Server.JimTestDomain.Flynetviewer.com and <port> is the port number that the SQL service runs on. This is typically 1433 (verify this by checking the IP address in TCP/IP properties of your SQL Service in the “Protocols for MSSQL Server” section of SQL Server Configuration Manager (SQL Server 2005 network congig.àProtocols...))
So an example of the command is:
e.g. “setspn –a MSSQLSvc/Jim-SQL-Server.JimTestDomain.flynetviewer.com:1433
s-SQLServer”
5.
On the Domain Server, use Active Directory Users and Computers to set the Delegation properties of the s-AppPool user. This will allow the application pool to present delegated credentials to the SQL Server service.
a.
Locate the s-AppPool user and double click to open the properties window.
b.
On the delegation tab (which should have appeared since you entered the spn’s in the previous step) select “Trust this user for delegation to specified services only”
c.
Select “Use any authentication protocol”
d.
Click “Add”.
e.
In the “Add Services” window, click “Users or Computers”, to locate the s-SQLServer user, and click “OK”.
f.
The “Add Services” box should now contain an entry for MSSQLSvc that you set up the Service Principal Name for. Select this, make sure it is highlighted, and click OK.
g.
The entry for the service should now show in the Properties window for the s-AppPool User Account. Press OK so that the new setting takes effect.
6.
On the Web Server, add the Application Pool user account to the IIS Worker Process group for that machine, to enable it to run the application pool.
a.
Click the “Start” button, right click on “My Computer”, and choose “Manage”.
b.
In the Computer Management snap-in, in the left hand tree diagram, expand the branch titled “Local Users and Groups”.
c.
Click on the “Groups” branch.
d.
In the right hand pane, find the group called “IIS_WPG” and double click on it. This brings up the properties window which shows the members for this group.
e.
Press the “Add” button to add a new user to the group.
f.
Add in the Application Pool account name from the domain, e.g. s-AppPool
g.
Press OK for this setting to take effect.
h.
7.
On the Web Server, use Local Security Policy tool to add extra security settings for the Application Pool user account, to enable it to run web service within the application pool.
a.
Run StartàControl PanelàAdministrative ToolsàLocal Security Policy
b.
In the Local Security Settings snap-in that appears, in the left hand navigation pane, open up the Local Policies branch, and click on User Rights Assignment.
c.
In the right hand pane, find the policy “Adjust Memory Quotas for a process” and double click on it to see the properties window.
d.
In the “Adjust Memory Quotas for a process” window that appears, in the Local Security Setting tab, click on the “Add User Or Group” button, and enter the name of the Application Pool user on your domain e.g. s-AppPool.
e.
Press OK and OK so that these settings take effect.
f.
In the right hand pane, find the policy “Replace a Process Level Token” and double click on it to see the properties window.
g.
In the “Replace a Process Level Token” window that appears, in the Local Security Setting tab, click on the “Add User Or Group” button, and enter the name of the Application Pool user on your domain e.g. s-AppPool
h.
Press OK and OK so that these settings take effect.
8.
On the Web Server, grant default permissions on the C:\Windows\Temp folder to the Application Pool user to allow it to run the web service.
a.
Open Windows Explorer and browse to C:\Windows\Temp
b.
Right click on the C:\Windows\Temp folder name in the left pane and choose “Properties” to bring up the properties window.
c.
In the “Security” tab, underneath the “Groups or User Names” box, press the “Add” button.
d.
Add the user name of the Application Pool user from the domain, e.g. s-AppPool and press OK. The user should appear in the upper box, which shows that it has Read, Read and Execute, and List Folder Contents permissions in the lower box.
e.
Press OK for the settings to take effect
f.
You may get a Security warning box asking if you wish to continue. Click “Yes” in this dialog box.
9.
On the Web Server, create a new Application Pool, and set the identity to the s-AppPool user account.
a.
In Administrative Tools, open up Internet Information Services (IIS) Manager.
b.
In the left hand pane of the IIS manager, right click on “Application Pools” and choose “New”à”Application Pool...”
c.
Enter a name for the new application pool, and use default settings for now (they can be changed later), and press OK to create the Application Pool.
d.
Right click on your new application pool in the left pane, and choose “Properties”
e.
On the “Identity” tab, set the Application Pool Identity to <yourDomainName>\s-AppPool and enter the password, where <YourDomainName> is the name of your domain. E.g. JimTestDomain\s-AppPool
10.
On the SQL Server, you may need to create Windows Authentication login accounts for each user of the system, if this hasn’t been done already.
a.
In Microsoft SQL Server 2005 Management Studio, open up the “Security” branch of the server explorer tree, and open up the logins branch to see the logins on the system.
b.
To add a Login, right click on Logins and choose “New Login”.
c.
In the New Login dialogue box, select “Windows Authentication” for the authentication type.
d.
Next to the login name box, press the search button and select an appropriate user name from the domain, e.g. JimTestDomain\Jim.Plenty
e.
Proceed with configuring other required properties for this login and press OK.
11.
Also on the SQL Server, you will also need to ensure that the logins are associated with a particular user account on the target database on SQL Server 2005, for each user of the system.
a.
In Microsoft SQL Server 2005 Management Studio, open the “Databases” branch in the SQL Server explorer tree. Locate your target database. I have used “TestDB”. Open this branch and within that open the “Security” branch. Within that, open the “Users” branch.
b.
Right click on “Users” and choose “New User”
c.
In the New User dialogue, please enter an appropriate user name for the user. E.g. “Jim Plenty”
d.
Now, next to the login name box, please press the browse button, to set the login name for the user, that you located in step 11. E.g. “JimTestDomain\Jim.Plenty”
e.
Now set the database roles required for this user. “Db_datareader” is normally sufficient, but if your queries contain INSERT statements you should add “db_datawriter” as well. Press OK to confirm the changes.
12.
When you publish Web Services from DWS 2010, on the Web Service tab, please press the “Advanced” button, and on the advanced settings page check the box where it says “Impersonate windows user for web methods”. This turns on impersonation on in the code of the web service, so that user credentials will be passed through to the database via the delegation that you have set up.
13.
Also in the advanced settings, set the IIS Application Pool for the web service to be published, as the Application Pool you created from step 6. Choose it from the dropdown, (E.g. JimAppPool) and press OK to save the settings. These settings will be permanently saved to your project file the next time you choose File/Save.
14.
Now, please generate your web service using the “Generate Web Service” button.
15.
In IIS on the web server, we have to disable anonymous access to the web service. This forces requests to the web service to run under the windows authenticated account.
a.
In Administrative Tools, open up Internet Information Services (IIS) Manager.
b.
In the left hand pane of IIS Manager, locate your web service in the Web Sites Folder, usually within the Default Web Site branch. Right click on it and choose “Properties”.
c.
In the properties of your web service, go to the “Directory Security” tab and press the Edit button in the “Authentication and Access Control” section of the page.
d.
The “Authentication Methods” window appears. Please uncheck the box that says “Enable Anonymous Access”
e.
Please note that in the “Authenticated Access” section on the same page, the box “Integrated Windows authentication” should be checked, and the other boxes should be unchecked.
f.
Press OK twice to close down the property window, so that these settings take effect.
16.
On the computer(s) that you are using to access the web services from, you may need to enable Integrated Windows Authentication on the web browsers. In Internet Explorer for example, go to ToolsàOptionsàAdvanced Tab, and in the Security section please make sure that the “Enable Integrated Windows Authentication” box is checked. If you check this you will need to restart Internet Explorer for the change to take effect.
Testing the setup:
In your web browser, you should now find that if you go to the ASMX page of the web service to view the available web methods, with the Web Server name explicitly contained in the URL (e.g. http://Jim-Web-Server/WebService_TestWS/WebService_TestWS.asmx), that you get a Windows credentials challenge box, with the title something like “Connecting to JimWebServer.JimTestDomain.flynetviewer.com”, and a box for user name and password. This shows that anonymous access has correctly been disabled.
If you carry out this test on the Web Server itself, with the server name in the url, enter the user name and password of one of the users you have set up on the system (e.g. JimTestDomain\Jim.Plenty).
On the web server, you should be able to test the Check License method by following the link and pressing the “Invoke” button, and get the message “License was available and has now been reserved”.
Now test one of the data methods that you have built into the web service. You should see data coming back from the database, when you press the invoke button.
Conclusion:
If all is well, this means that the user account that accesses the database is the user that accessed the web service, rather than the anonymous user or the application pool identity. So now, depending on your database structure, you can modify the data returned to dashboards via the web services, on a per-user basis. This can be implemented in your web service queries, by using the SYSTEM_USER system variable.
For example, sql queries of the following form may be useful:
Select SUM(Projects.ProjectCost), Projects.Country from Projects
Inner Join ProjectsAccess On Projects.ProjectID = ProjectAccess.ProjectID
Where ProjectAccess.User = (Select SYSTEM_USER)
Group By Projects.Country
In the above example the ProjectsAccess table contains ID’s of projects that each user is authorized to access data on, together with the Domain\UserName. The contents of this table will then influence the outcome of the total cost calculation for projects that apply to each user, in the simple query.
