Printing from Reporting Services – Back to Basics

Printing from Reporting Services – Back to Basics

Reporting Services has never struck me as something that it\’s desirable to print reports from. I don\’t see the point (if you\’re keeping historical reports), and these days we\’re all about saving the planet by reducing the amount of office consumables we…… erm…….consume.

However, that said, it may be a requirement to have a hard copy of something from Reporting Services, so Microsoft have usefully provided a \’print\’ button in right there in the browser window. Happy Days !

Or not.

I was a bit flummoxed the other day when I got a call about \’my Reports won\’t print off. All I get is a rubbish error message.\” With a heavy heart, I set to, getting the proper text of the said rubbish error message, which was this:

\”An error occurred during printing (0x80004005)\”

A nice, concise, and helpful explanation, I thought.

OK, so what does your favourite search engine have to say about THAT ? Plenty, as it turns out. Mostly to do with the RSClientPrint DLL that\’s downloaded (if you\’ve enabled this option on your Reporting Services server), it being the right version, the browser having permission to download and run signed ActiveX scripts, and a whole pile of KB articles about what you can hack about with if you see this message.

One of the things it won\’t tell you is that you need to have a default printer set within your Windows user profile, otherwise the \’print\’ button won\’t even get as far as letting you choose an output device (or \’Printer\’, as they used to be called).

So, notwithstanding all the excellent advice in that heap of KB articles, MSDN forums and StackOverflow, sometimes you just have to got back to first principles, like setting up a default printer.

The only thing you have to contend with then is the general complexity of the report, whether or not it will render properly in PCL (do they still use PCL to talk to printers ? I\’ve forgotten…), and all the other stuff in those online resources I just mentioned.

Oh, and if you still can\’t get it to print from the button, try asking these two questions:

\”Why do you need a printout of this 1400-page report ?\”

…and…

\”Can\’t you just export the report to PDF, and print that, because that method works OK ?\”

Just try to resist storing that 1400-page PDF in Sharepoint, if you don\’t mind.

Back soon !

KERBEROS and OPENROWSET – Be Careful What You Wish For

KERBEROS and OPENROWSET – Be Careful What You Wish For

If, like many institutions these days, you\’ve got a shared or consolidated SQL Server environment, you would be forgiven for thinking that everything will rub along just peachy together, with your MIS databases happily running alongside your Sharepoint content databases, for example (it\’s only a database after all).

However, there are certain situations where system demands are in direct conflict, so I thought I should point one of them out that happened recently.

So, there we were, happily running (amongst others) an MI database and a Sharepoint content database on the same instance. So far, so good – doubles all round !

Until we had a problem with the Sharepoint system performance, that is. As I\’m sure you know, Microsoft Best Practice for Sharepoint is to use kerberos as the authentication protocol throughout – from Web Front End, through Web App, right down to the database layer. So, like diligent DBAs (and semi-diligent Sharepoint Admins), we created an SPN for the SQL Server domain user account so that kerberos authentication would work down at the database end. Hey presto, Sharepoint performance gain, and another round of doubles ordered !

Next morning, with a bit of a headache if I\’m honest, there were several fault tickets raised against the server we had just enabled kerberos on, but they were not for Sharepoint, they were for the MI database – several overnight import jobs had failed.

And the problem was this – the import jobs all used OPENROWSET to connect to a remote server to grab data to use in the MI. OPENROWSET works in a kind of funny way, in that when the required provider string is set to \”Trusted_Connection=yes\”;, OPENROWSET will attempt to send the credentials of the calling process through to the remote server. (Actually, it may not, but this is how I\’m going to explain it. It\’s my blog).

Using good-old NTLM authentication, this is not so much of an issue. As long as the calling process has rights on the remote database, then it\’s happy days and your OPENROWSET will work. I know this, because that\’s exactly what was happening before kerberos was enabled.

However, using kerberos to do the same thing, you MUST trust the account that the SPN was created with for delegation in Active Directory – if you don\’t, the call to OPENROWSET will strip the username and password of the calling process, and send \”NT AUTHORITY\\Anonymous Logon\” as the user credentials (which, surprisingly, probably won\’t have any permissions on the remote database). That\’s even if you\’re making the call to OPENROWSET using SQLAgent that runs under the same account as registered in the SPN (effectively then, passing it\’s own credentials to itself). Bummer.

Again, like many institutions, right now we\’re not allowed to enable delegation in Active Directory here. Apparently, it\’s a security risk (not my words, you understand, it\’s the InfoSec guys). Since the account that the SPN was registered with was not trusted, the only workaround available was to fall back to SQL authentication in the provider string \”UID=mySQLUser;PWD=notverysecurepassword\”, and remove the \”Trusted_Connection=yes\”;. That actually looks like more of a security risk to me, but what do I know ?

I\’ll not go into why OPENROWSET was used over, say, linked servers or SSIS. But rest assured, that\’s where the dev guys will be heading right about now, so that we don\’t have to have a user id and password in clear inside a stored procedure.

So, to sum up, I guess what I\’m saying is that you really should understand all of the databases on a shared instance before you go and make changes to the setup of SQLServer security.

But you already knew that, didn\’t you ?

Back soon…