Impersonation, Single Sign-on, and SPS
Abstract
The scenario is a common one: An organization has some LOB application that is critical to their everyday needs. In fact users find themselves logging in periodically just to check one particular screen of information. Often, this information is summary data within the system. This article will detail a solution to build a web part that pulls this information into the corporate portal increasing user productivity by giving them access outside of an otherwise siloed application. In this example, the summary data is equally available for all users; therefore the web part will impersonate a specific windows account that has been granted access to the data. The solution will accomplish this using SharePoint Portal Server's single sign-on service.
To fulfill the above scenario, the solution will execute the "Ten Most Expensive Products" stored procedure from the Northwind datbase using a specific windows credential for all users and display the results in a SPS web part.
Setup
This article assumes that the reader has already enabled and configured SharePoint's single sign-on service. If you are having problems, the following URL is a great reference: https://www.microsoft.com/technet/prodtechnol/sppt/reskit/c2661881x.mspx#EDAA
This article also references the SAMPLE active directory domain. Substitute an actual domain and accounts in its place.
Once SSO is setup. You will need to create an enterprise application definition. Since we are mapping all users to the same credential, the Account Type should be Group as opposed to Individual. The other fields are as follows:
DisplayName: Northwind
ApplicationName: Northwind
e-mail address: something@sample.com
Field 1:
Name: SQL Server
Mask: No
Field 2:
Name: Database Name
Mask: No
Field 3:
Name: User Name
Mask: No
Field 4:
Name: Domain
Mask: No
Field 5:
Name: Password
Mask: Yes
With this definition created, you can then enter the Account Information for the definition. This is where you specify an entry for all the fields in the above definition. For the Northwind application, enter a group of SAMPLE\Domain Users. And then specify the actual fields. For this solution, the following fields were used.
SQL Server: SPSPlay
Database Name: Northwind
User Name: NorthwindSvs
Domain: SAMPLE
Password: #######
Finish up by making sure you actually have the windows account created and that it has been granted login rights to the specified database server as well as rights to the Northwind database.
The Code
Just so you can follow along, I have posted a zip file containing the web part code as well as some additional classes. You can obtain the zip at the following URL: https://h1.ripway.com/edhild/sso_sample.zip. The following describes the classes and highlights some key elements in the code...
SSONorthwindHelper class
This class wraps the retrieval of the application definition from SPS SSO. It has one important method LoadCredentials() that gets the SSO information and populates read-only fields so that it is available for the web part. In addition, this class creates an instance of the ImpersonateHelper class explained later.
public void LoadCredentials()
{
string[] rgGetCredentialData = null;
Credentials.GetCredentials(1, SSO_APPLICATION_NAME, ref rgGetCredentialData);
if (rgGetCredentialData == null || rgGetCredentialData.Length != 5)
{
throw new ApplicationException("Did not receive expected results from SSO Repository.");
}
ImpersonateHelper imp = new ImpersonateHelper(rgGetCredentialData[SSO_FIELD_USER],
rgGetCredentialData[SSO_FIELD_DOMAIN],rgGetCredentialData[SSO_FIELD_PASSWORD]);
this.m_databaseServer = rgGetCredentialData[SSO_FIELD_DBSERVER];
this.m_databaseName = rgGetCredentialData[SSO_FIELD_DBNAME];
m_impersonator = imp;
}
ImpersonateHelper class
This class handles the unmanaged call to LogonUser which establishes the identity which we want the web part to impersonate when accessing the remote resource. It also maintains a WindowsImpersonationContext so that after this work is done, the web part can call undo to revert to the previous identity context. Most of this class' code is an application of the sample published by Jay Nathan at the following URL: https://www.15seconds.com/issue/040511.htm
public void Impersonate()
{
// authenticates the domain user account and begins impersonating it
this.impersonationContext = this.Logon().Impersonate();
}
private WindowsIdentity Logon()
{
IntPtr handle = new IntPtr(0);
handle = IntPtr.Zero;
const int LOGON32_LOGON_NETWORK = 3;
const int LOGON32_PROVIDER_DEFAULT = 0;
// attempt to authenticate domain user account
bool logonSucceeded = LogonUser(this.m_user, this.m_domain, this.m_password, LOGON32_LOGON_NETWORK,
LOGON32_PROVIDER_DEFAULT, ref handle);
if(!logonSucceeded)
{
// if the logon failed, get the error code and throw an exception
int errorCode = Marshal.GetLastWin32Error();
throw new Exception("User logon failed. Error Number: " + errorCode);
}
// if logon succeeds, create a WindowsIdentity instance
WindowsIdentity winIdentity = new WindowsIdentity(handle);
// close the open handle to the authenticated account
CloseHandle(handle);
return winIdentity;
}
public void Undo()
{
// revert back to original security context which was stored in the WindowsImpersonationContext instance
this.impersonationContext.Undo();
}
SSOSample web part class
There are a few nice techniques demonstrated in this web part. The first is the use of GetRequiresData and GetData to setup an async worker thread where the database call will be made. Quoting the SPS SDK: A Web Part should use an asynchronous thread for any operation that could take a significant amount of time. In particular, if a database or HTTP request is made, an asynchronous fetch allows other parts to continue processing without being blocked.
public override bool GetRequiresData()
{
return true;
}
public override void GetData()
{
System.Threading.WaitCallback w = new System.Threading.WaitCallback(this.Process);
this.RegisterWorkItemCallback(w,null);
}
Then all of the hard work comes in the Process() method. This method uses the SSONorthwindHelper to get the info from SSO and then uses the ImpersonateHelper to establish the running identity. Once impersonation is established, the database call is made using Microsoft's Data Access Application Block 2.0. The results are fed into a dummy data grid through a data reader.
protected void Process(object state)
{
IDataReader dataReader = null;
SqlConnection conn = null;
try
{
SSONorthwindHelper sso = new SSONorthwindHelper();
sso.LoadCredentials();
sso.Impersonater.Impersonate();
string connString = String.Format("Server={0};database={1};Integrated Security=SSPI;Persist Security Info=false;",
sso.DatabaseServer, sso.DatabaseName);
conn = new SqlConnection(connString);
dataReader = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "Ten Most Expensive Products");
grid = new DataGrid();
grid.DataSource = dataReader;
grid.DataBind();
this.Controls.Add(grid);
sso.Impersonater.Undo();
}
catch (Exception ex)
{
errorText = ex.Message;
}
finally
{
if (dataReader != null && !dataReader.IsClosed)
dataReader.Close();
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
Once deployed, enjoy the results!
Plus, you can use SQL Profiler to confirm that the specified windows account is being used. View Picture
Extensions
You could even make this more interesting if you change the scenario so that users have to provide a user specific credential that is not their current network identity. This framework works as a good starting point, but you will have to work in providing a link to the SSO form for users to enter their credentials. Maybe a follow up article is in the future...
Comments
- Anonymous
February 21, 2005
Refer:
http://blogs.msdn.com/roberdan/archive/2005/02/21/377477.aspx
Many THANKS! - Anonymous
February 23, 2005
I can't seem to get this to work. I'm getting Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Any ideas on what I'm doing wrong? - Anonymous
February 25, 2005
One other question, in your example are you running both SharePoint and the database on the same server? Perhaps this is why I'm running into the error.