Options
Backup reports not working across multiple servers...
fatherjack2
Posts: 311
Hi,
I have just tried the Reports (Tools | Reporting) function and am having trouble with multiple server reports.
I get:
"login failed for NT AUTHORIYT\ANONYMOUS LOGON" (unless i use the sa account + pwd in the linked server security) in the dialog that appears between clicking Generate Report and the report being displayed.
What security permissions does the linked server login need to the 'other' servers? Surely it accesses using the account that is running the SQL Backup service on the first server? I have that set to a network 'service' logon (ie not a specific person login) that runs all SQL services so why is the error mentioning Anonymous Logon ??
Development request:
is it possible to have the reports in an SSRS format that we can deploy/access via a SQL RS instance? I have a set of reports that i use to keep an eye on my SQL Servers and their activities/performance etc and having the SQL Backup reports there (hey, they can be scheduled and everything!!!) would make a certain amount of sense - in my world any way!
thanks
Jonathan
I have just tried the Reports (Tools | Reporting) function and am having trouble with multiple server reports.
-
I have created a SQLBackupReportingDatabase on one server and can report from that server with no problem.
I can report on any other server that is SQL Backup licensed - individually.
I
I get:
"login failed for NT AUTHORIYT\ANONYMOUS LOGON" (unless i use the sa account + pwd in the linked server security) in the dialog that appears between clicking Generate Report and the report being displayed.
What security permissions does the linked server login need to the 'other' servers? Surely it accesses using the account that is running the SQL Backup service on the first server? I have that set to a network 'service' logon (ie not a specific person login) that runs all SQL services so why is the error mentioning Anonymous Logon ??
Development request:
is it possible to have the reports in an SSRS format that we can deploy/access via a SQL RS instance? I have a set of reports that i use to keep an eye on my SQL Servers and their activities/performance etc and having the SQL Backup reports there (hey, they can be scheduled and everything!!!) would make a certain amount of sense - in my world any way!
thanks
Jonathan
Senior DBA
Careers South West Ltd
Comments
The error you are receiving while reporting on multiple servers is covered in the following knowledge base article :
http://www.red-gate.com/supportcenter/C ... 000229.htm
I hope this helps.
Redgate Foundry
Not really.
I have never been 'fluent' with Linked Servers as we dont need them a great deal here and your help page seems to be the same as the link to microsoft supplied at the bottom only without the formatting...
I have SQL Backup running as a service, using a network logon (eg. domain\SQLB5Admin). This account has high level server access to all SQL Servers - its a member of the serveradmin role.
In the linked server properties on ServerA I have alternately set the Security tab to have:
Local Login - domain\SQLB5Admin
Impersonate - ticked
RemoteUser - empty
RemotePassword - empty
and
Local Login - domain\SQLB5Admin
Impersonate - unticked
RemoteUser - ServerB_SQL_Login
RemotePassword - ****
and i get the same error.
I can only get this to work if i complete further details in the section marked "For a login not defined in the list above, connections will:"
here, if i check "Be made using the security context:"
and complete the "Remote Login" and "With password" with the ServerB_SQL_Login details, the reports work.
My understanding of this situation is that the process requesting data from the remote server (ServerB) doesnt have a local login mapped to the remote server and so uses the one specified.
If this is the case surely anyone and everyone attempting to get to the remote server from the local server would get their connection created as ServerB_SQL_Login and therefore assume its elevated priviledges??
Why doesnt the service account running SQL Backup have its login mapped across to the linked server?
I may be misunderstanding SQL Server Linked Servers security and if so i apologise but security of our data has to take precedence.
footnote:
We are using Windows server 2003 R2 64 bit Enterprise with SQL 2005 Standard 64bit sp2 and the microsoft article is dated Jun 2004 and refers to Windows NT4.0 and SQL 2000. Maybe it is still relevant but ...
Senior DBA
Careers South West Ltd
have to leave work soon so only a short explanation...
when you run reports for multiple servers you as a domain account are requesting data from SQL Servers (behind the scenes there are extended sp's that go via dll's to interrogate log files) and as such your network credentials are used.
the 'double hop' mentioned in the comment/article above applies but the linked server security needs to be applied to the account of the person requesting the report...
on serverB (or indeed C etc ...) create a local SQL logon for SQL Backup to use, ensure that this logon is a datareader user in the master datbase and specifically execute permission on the master.dbo.sqbdata extended stored procedure
on serverA add a linked server and for the domain account or group that relates to the users who will be running reports from SQLBackup, create a mapping for them to login to ServerB as the SQL login created above.
hope this is accurate and helps out some people.
RedGate - please alter / amend as necessary to better explain how SQL Backup is gathering the data.
Jonathan
Senior DBA
Careers South West Ltd