How to make a connection with a database

To retrieve data from a database, you have to make a connection first with it. But for some reason, sometimes you don't want to embed connection strings in your web.config (and code), but how are you going to manage this?

One option is to use Single Signon for this. But what IS sso and how does it work?

Single Signon is a microsoft service that can store various (logon) information in a database. The only thing that is needed , is to specify a user(admin? ;)) that is allowed to maintain this information, after that, it's a piece of cake to use.

When the sso service is started, usernames, passwords and other information, for example databases, can be specified to be used. When this service is used, the information that is looked for will be returned and can be used to signon to, for example, a database. Using sso, no data needs to be stored in the web.config and all logon information can be stored in a central place. The great thing about this, is that groups can be specified that are allowed to retrieve is information.

How does this work for a sharepoint user that doesnt have the rights to acces a database?

* At first, enable to Single signon service: Open the services window in the administration panel and open the Microsoft Single Signon Service. Set the startup type to automatic. Go to the Log On tab, select "This account" and fill in the administrator account.
* Go to the Sharepoint Central Administration page and select the Operations tab.
* In the Security Configuration, select "Manage settings for single sign-on"
* Open "Manage Server Settings for Single Sign-on"
* Single Sign-On Administrator Account: the administrator account that you also filled in in the Single Signon Service.
* fill in the same account for Enterprise application definition administrator account
* fill in the database server and database name that is storing sso information
* use the default timeout settings. Press OK
* Next, create an encryption key
* After the creation of a key, navigate to the Manage Enterprise application definitions.
* Create a new item
* Fill in the display name (for example sql acces
* application name, the string that is used to identify the data
* email address for support
* select a group account type
* select windows authentication as authentication type
* In logon account information, fill in the fields that will be filled
* Field 1: username, mask: no
* Field 2: Password, mask: yes
* Field 3: database name
* When the data is created, select the "manage account information for an Enterprise Application Definition
* Select the application definition that you just created and fill in the group that will have acces to the credentials for logging in to the database
* You will see the 3 fields that you created: username, password and database name
* Fill in the credentials that are used to log in onto the database.
* Now you will be ready to write some code to retrieve the credentials, when you are allowed to get them

example code:


private WindowsImpersonationContext LoadCredentials()
{
string[] rgGetCredentialData = null;
Credentials.GetCredentials(1, "encisql", ref rgGetCredentialData);

if (rgGetCredentialData == null)
{
throw new ApplicationException("Didn't get CredentialData");
}

string _userName = rgGetCredentialData[0];
string _password = rgGetCredentialData[1];

string[] userDomain = _userName.Split('\\');
IdentityHelper.IdentityHelper ih = new AjaxWebParts.IdentityHelper.IdentityHelper();

before = WindowsIdentity.GetCurrent().Name;
WindowsIdentity id = ih.CreateIdentity(userDomain[1], userDomain[0], _password);
WindowsImpersonationContext impersonatedUser = id.Impersonate();
after = WindowsIdentity.GetCurrent().Name;

return impersonatedUser;
}

private void GetData()
{
WindowsImpersonationContext impersonatorID = LoadCredentials();

// code to retrieve data from database

UndoCredentials(impersonatorID);
}



* Now you can logon to the given database, with the credentials of the account that is allowed to logon.