2

I've created a report that targets 2 databases "A" and "B". In my report, I'm using a static datasource connection to database "A" and a dataset that also uses this datasource. This datasource uses windows authentication. On database "A" I have linked server "B" to it using this line of code: exec sp_addlinkedserver @server='', @srvproduct='', @provider='SQLNCLI', @datasrc=''. On "A" I have created a working sproc that executes queries successfully for both "A" and "B" in SSMS.

In my report, I have a @ServerName parameter and it passes the proper value to the sproc. I know its working because server "A" works within my report using the "Preview" tab and when deployed. But server "B" always gets the same error message per the title. I think it has something to do with my static connection string, but since the sproc works shouldn't it only need a connection to server "A" only?

I tried to be as thorough as possible I've been fighting this for 2 days and would love to get this simple problem nipped in the bud, thank you very much.

tshoemake
  • 1,311
  • 1
  • 17
  • 28
  • You are running into the infamous double-hop issue as described [here](http://stackoverflow.com/questions/13706580/kerberos-double-hop-in-asp-net-4-0-sql2008r2). – Bryan Jun 20 '14 at 19:58
  • It doesnt involve IIS though. – tshoemake Jun 20 '14 at 20:31
  • 2
    Doesn't matter, you're still passing credentials from SSRS -> ServerA -> ServerB. The SPNs required to resolve Kerberos delegation issues from Client -> IIS -> Database will be the same. Even though SSRS isn't using IIS, you need an HTTPsvc SPN for the report server in addition to SQL Server SPNs. Or use static security context in the linked server properties and don't mess with SPNs. – Bryan Jun 20 '14 at 20:41
  • OK, thank you for the reply I'm currently waiting for a static account to be created and test that out. – tshoemake Jun 23 '14 at 17:45

1 Answers1

3

The problem is basically this:

You connect to ServerA -- but it's not passing your credentials to ServerB.

Take a look into Linked Server Security and sp_addlinkedsrvlogin.

One approach is to setup impersonation, where any connection from user 'x' (what you pass in your connection to ServerA), uses a specific user/pass to connect to ServerB.

Another more dangerous approach (screen shot), is to set ANY connections to ServerB to use a static user/pass... at the very least, this can be used as a quick and dirty test.

Linked Server Properties

Dave C
  • 7,272
  • 1
  • 19
  • 30
  • I forgot to mention I also have an Execution Account added under Report Services Configuration Manager for Server A... would that come into play at all? My connection string is simple, just a connection to ServerA and dbname, using windows auth. – tshoemake Jun 20 '14 at 20:01
  • 1
    Those would be the credentials not being forwarded to ServerB. If you setup a local mapping on ServerA to use a valid ServerB account, it should be fine. See screen shot (large white mapping box w/ local user to remote user/pass data). – Dave C Jun 20 '14 at 20:10
  • Waiting on sql server admin to create an account to test, will let you know results. Thanks for the help I hope it works. – tshoemake Jun 23 '14 at 17:51
  • 1
    I had my SQL admin create a sql account with read only permissions, added the credentials in the Linked Server Properties box and also switched my report's datasource from Windows Authentication to those newly created credentials. Thanks for the help JiggsJedi and beargle. – tshoemake Jun 23 '14 at 19:26