In this post, we’re going to take a look at the data source credentials screen in SQL Server Reporting Services. In the monthly reporting services class I teach, I’ve noticed that this screen is often a source of confusion for students. There are several credential options available when creating a data source and depending on what type of data source you’re connecting to, and how your environment has been configured, some of them might not work. The 4 main option available are:
- “Use current Windows user. Kerberos delegation might be required”
- “Use this user name and password”
- “Prompt for credentials”
- “Do not use credentials”
All of these options are found on the “Data Source Properties” screen on the “Credentials” tab when creating a data source. To open this screen in Report Builder follow these steps:
- In the “Report Data” window, right click on the Data Sources folder and then click “Add Data Source”
- Click the “Credentials” menu option
The resulting screen should look similar to the screen shown below:
Let’s take a look at each of these options one at a time and examine their usage.
“Use current Windows user. Kerberos delegation might be required”
This option uses the ID of the user viewing the report to authenticate to the reports underlying data source. So when using this option, every viewer of the report must also have permissions to connect to and view the data that the report shows. So if you’re using this method and some people are able to view the report just fine, while others are getting errors, it might be the case that some user accounts do not have the proper permissions to read data from the reports underlying data source. It could be that the users experiencing problems are missing read access to the entire data source or perhaps only subsections of the data source, like not having read permissions on a specific table within the data source.
As this option mentions in its title…”Kerberos delegation might be required”. This may indeed be the case if you’re reporting off of a data source that is not on the same server as your reporting services installation. For example if your Reporting Services installation is configured in SharePoint integrated mode, and your report pulls data from your companies data mart that exists on a separate server this option will not work unless you have Kerberos configured between your reporting services installation and the server that your data resides on. This is commonly referred to as the “double hop” scenario. The issue is that credentials cannot be automatically delegated more than one server (one hop) away. In the scenario described above our authentication path is:
[User Machine] >>>> [SharePoint SSRS WFE] >>>> [Data Mart Server]
If Kerberos is not configured in this scenario, this option will not work and you’ll probably get an error similar to one of the following:
- Unable to connect to the data source. Please verify the connection properties and credentials.
- Can not convert claims identity to windows token. This may be due to user not logging in using windows credentials.
- An error has occurred during report processing. (rsProcessingAborted). Cannot impersonate user for data source 'DataSource1'. (rsErrorImpersonatingUser). Can not convert claims identity to windows token. This may be due to user not logging in using windows credentials. (rsClaimsToWindowsTokenLoginTypeError)
Configuration of Kerberos will require the use of a domain admin account and the creation of multiple service principle name (SPN’s) and can be a rather complicated configuration. That said, having Kerberos configured is widely considered one of the more secure methods of authentication since everyone is connecting to the data source as themselves.
If the scenario above sounds familiar but Kerberos is not configured in your environment don’t worry, there are other means of getting around the double hop scenario that we’ll discuss in the next sections.
If you happen to be running SSRS in SharePoint integrated mode and are pulling your data from a SharePoint list in the same farm, this option can be used as your method of authentication without the need of Kerberos.
“Use this user name and password”
Instead of having every user connect to underlying data sources as themselves as described in the previous section, this option has everyone connect to the data source as the same account. To use this method you will need to create an account that can be used as a services account during authentication. In general a service accounts used for reporting should have the following properties:
- The account has only read access to the data sources it’s reporting from.
- The password is set to not expire
It’s important to use a service account rather than a real user account because employee accounts should be deactivated when employees leave the company. So unless you want all your reports to stop working when “Joe developer” leaves the company, you should go through the effort of creating a service account specifically for reports to use. It’s also common for organizations to require employees to change their passwords on a regular basis, which would require you to update your data sources every time a user’s password is updated if you used an employee account instead of a service account.
In some scenarios you might even want to create multiple services accounts for different areas of the company. For example your human resources (HR) department might have reports that access sensitive information about employees that not everyone should see. In this case it would be a good idea to not only secure the reports within SSRS so that only members of the HR department can access them, but also create a service account specifically for the HR reports, rather than have 1 service account that access everything. In general you want your services accounts to have as much access as they need to perform their functions….and no more.
Services account can be created in either Active Directory or the data source you’re connecting to. Note that not all data sources have the ability to create user accounts within them though….SQL Server Analysis Services for instance does not have this capability, while a regular SQL Server database does. When configuring this option using an Active Directory account, you’ll want to include the domain name before the user ID. For instance if my domain was “MyDomain” and my user ID was “UserName” you would enter in the user name as “MyDomain\UserName” without the quotes. You’ll also need to check the “Use as Windows credentials” if you’re using an Active directory account. If you’re using an account you created in SQL Server for example, you would simply enter in the user name, leaving off the domain and not check the “Use as Windows credentials” option.
The “Impersonate the authenticated user after connecting” option can be used if you’re looking for a way to get around the double hop scenario without having to configure Kerberos. With this option checked the report will authenticate to the data source as the service account you entered, however when the report queries data it will do so as if the user authenticated to the data source as who they actually are. Behind the scenes this is done via the SetUser() SQL function at execution time. John White has a fantastic blog post on the usage of this option at http://whitepages.unlimitedviz.com/2012/09/using-per-user-identity-with-reporting-services-and-sharepoint/ so rather than repeat what he’s already done quite well I’ll just point you to his post. If you plan on using this option you should go read his post as there are some specific requirements regarding the service account you use with this option and what role it need to be in.
“Prompt for credentials”
This options is pretty self-explanatory. If you would prefer users get prompted to enter credentials every time a report is run, this option will allow for that functionality. However, if you think prompting users to enter in their credentials every time they want to run a report is going to be a good work around for having users authenticate as themselves in environment where Kerberos is need but not configured….”you’re gonna have a bad time”. It will indeed work from a technical standpoint, but there will be unrest among the report users.
Having to enter in your credentials all the time is a major drag. Don’t just take my word for it though, feel free to try it…..you might want to be a little suspicious of any report users who offer you complimentary baked goods out of the “kindness of their heart” though.
Just like the “Use this user name and password” option, you also have ability to use Active Directory accounts or SQL database account with this option. Which type of account to use will need to be determined by the report developer, as it can’t be adjusted by the report viewer when running the report. Just make sure to check/uncheck the “Use as Windows credentials” appropriately as described previously. Once again if you are using Active Directory accounts with this option you will also need to enter in the user name as MyDomain\UserName. User may not automatically know what type of account they’re being prompted for when running the reports. Fortunately when using this option you are allowed to enter in “Prompt Text” to inform the user of what they’re being prompted for. If you go this route, you’ll want to make this as detailed as possible to avoid additional confusion.
“Do not use credentials”
Not all data sources will necessarily require authentication, although in a corporate environment this is rarely the case. In the event you find yourself calling to data that doesn’t require any type of authentication (like perhaps a publically available web service using the XML connection type) simply choose this option.
Well hopefully this clears up a little confusion on what your options are when connecting to data sources with Reporting Services. Feel free to leave questions in the comments, and happy reporting!