Muller Jannie

About myself and everyone else.

Reporting Services

Maybe you have seen this.

Login failed for user ”. when using unattended execution

The report execution x67wqt55acfewcugepmvrkn has expired or cannot be found. (rsExecutionNotFound).

Perhaps you have had a good dig around in report security, I’ve read enough security articles on reporting services security to make my head hurt. There is ALWAYS something to blame. If it’s not double-hop kerberos authentication then it’s the domain account not having the correct privileges.

Then you also have to deal with Windows Users, part of a Windows Group and how their security is delegated through reporting services, remote reporting servers and eventually how this user connects to the database.

Key options we want to look at achieving.

ReportSecurity

1 – We have a NT user, Domain\Jannie. I don’t have SQL access but I need to be able to refresh a report with a data source pointing to a database.

You have an option here to create the Domain\Jannie in the SQL database with read only permissions, the you use. In the screenie on the right you can use the option, credential supplied by the user, and also “use as windows” credentials. Sure this will work.

This will require maintenance and tracking of your newly added DB user.

But now what if Bob also wants to see the report. Domain\Bob, you can also add bob to the SQL server database with the required read permissions. We now have to manage 2 users on SQL. These users will get restore whenever the source database is, although the SIDs will be out of sync you always have this dirty bunch of users hanging around. I like tidy database.

So what’s the options, you can get you friendly domain administrator to create an NT group, this is tricky, because bob and Jannie belongs to different departments, so your NT groups becomes messy also.

You also need to manage this as users move between departments, after 6  months of doing awesome stuff, Bob gets promoted and no longer needs to see the reports. Do you diligently notify the domain admin in order to remove Bob ?, ahem.. do you reeaaly ?

2 – SQL logins \ Windows Login (stored on server)

So instead of creating a user bob or Jannie in the SQL DB you can create a user for the report source. The common “ReportUser”. You can then give the required SQL access to this user and as people come and go, on the DB side you are still good. Using this option with the radio buttons and check boxes will store the user details on the report server.  I think this is better since you don’t have to individually maintain member access.

3 – Unattended Execution account

I honestly thought this is the holy grail for a business where you don’t care too much about sensitive data. You can create the account in reporting services configuration, this is a domain user, it has to be.  If you can’t do this through the GUI you can modify the rs config files to specify the domain account, you can have a look at some of the xml for that in the screenie.

uea

Once you have done this you need to also modify the “no credentials” required on the specific data source, when you do this reporting services will use the unatten

ded account. With this account you could run into the kerberos issues, there is also a whole list of prerequisites for allowing account delegation which needs to be setup on the user. I have not been able to find a single, concise list of the local security policy options required for this, in most instances admins end up adding more than what is required. Giving this sort of access is obviously a problem.

Modifying the config file for report server and changing the unattended execution account also requires a restart of the service. You might be lucky to do this any time you want but you also may be in a position where you can’t.

Using the method of the rs config file you also end up with a clear text password which is not super great. This is a server level account. So unless you specify other SQL\Windows users credential you basically have setup yourself with a catch all account. Thus if this is compromised, then you have a breach on all of your data sources,which is also not super great. You might also get this error Login failed for user ”. when using unattended execution, even if it would appear that you follow everything in the help guides and patched up to the hilt and also exhausted MSDN (if that is possible) and kerberos(3 headed dog guarding the underworld) architecture on wikipedia and other fun things like domain user configuration and local + account policies *phew*.

The solution to my puzzle.

Honestly I’ve decided to skip the above since there is no consistent articles that seems to fix it.  I like consistency.

I stuck with create a SQL user from a Domain account, i.e. Domain\. This is a user which I can manage the security for , I can do a audit on SQL by the name, and combining data from various instances will give me the entire footprint of the access of this user. I can also verify members (see domain security post) and audit them.

Sure, you can do all of this with the other users, yup.

However, I then proceed to exclude any of the radio buttons and check boxes in the report server, data source editor. I use the “no credentials required option”. What ? Isn’t this reserved for unattended execution account, yes it is.

However in the connection string I do my authentication. This can be done as seen in the screenshot, I specify the uid=<Domain\DataSource> and I use integrated security. How is this different from “store credentials at the report server”. This also saves you from typing out the password and storing it.

This allows me to run my reportserver as a local service or basic account with SQL access. I also don’t have any obscure problems with “” user login failures even though the user exists. I don’t have issues with stale DNS entries or local \ account security  policies.

It’s straight forward and consistently works.  It does come with the risks that a breach could provide access by means of this impersonation where a windows account won’t. Note you can also limit the report folders to specific accounts so there is plenty of tying down you can do.

I hope this helps someone struggling with the 3 headed beast, (not the domain admin) the kerberos related config issues .

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on January 30, 2013 by in SQL Server and tagged , .
%d bloggers like this: