SMS Web Reporting Across Databases

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

SMS Web Reporting Across Databases

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> SMS Web Reporting Across Databases

Page: [1]

Message

<< Older Topic Newer Topic >>

SMS Web Reporting Across Databases - 7/29/2008 11:25:03 AM

erentz

Posts: 6
Score: 0
Joined: 7/29/2008
Status: offline

Has anyone connected separate databases in a SMS web report?

I have a database residing on the same SMS server that I would like to join to SMS tables and access through the SMS web report. Currently, I'm getting the following security error in the SMS web report interface: Server user 'NT AUTHORITY\SYSTEM' is not a valid user in database ProductExclusion.

I'm guessing it is an SQL security issue related to the account the web reporting tool uses to access the databases. I want to grant the web reporting account access to the external database, but I'm not sure what account should have access and what level of access.

I've successfully used the query that crosses databases through query analyser, but that is no surprise as I have access to both databases.

There was a similar problem with the SMS Client Health database and the link is included below. It doesn't appear to resolve the issue, but provides a reference.
http://myitforum.com/cs2/blogs/dhite/archive/2007/10/28/creating-a-sms-client-health-monitoring-tool-web-report.aspx

Any assistance would be appreciated.

Post #: 1

RE: SMS Web Reporting Across Databases - 7/29/2008 11:56:22 AM

jwilliamson26

Posts: 49
Score: 0
Joined: 1/23/2006
From: Kansas City Area
Status: offline

I know this is probably not supported, but instead of creating a query to pull the data from both DB's, we set up a SQL task to periodically copy the data from our Client Health DB into a custom table in our SMS database. The table name starts with ZZ and none of the other tables reference it. There have been no issues with it since we implemented it.

(in reply to erentz)

Post #: 2

RE: SMS Web Reporting Across Databases - 7/29/2008 12:53:49 PM

erentz

Posts: 6
Score: 0
Joined: 7/29/2008
Status: offline

Thanks.

I was considering that approach as my next option if I can't get the afforementioned working.

I was hoping to keep the data in a separate database to avoid any support issues and isolate the data from potential upgrades or disasters. Having it seperated keeps it isolated and easier to manage during a upgrade of disaster. Well atleast a little easier, I understand both are essentially the same.

(in reply to jwilliamson26)

Post #: 3

RE: SMS Web Reporting Across Databases - 7/29/2008 1:45:24 PM

gjones

Posts: 844
Score: 50
Joined: 6/5/2001
From: Ottawa, Ontario, Canada
Status: offline

Do you care of "guest" can read the data from the Client health db?

_____________________________

Garth@enhansoft.com

For a List of my Articles
http://www.myitforum.com/contrib/default.asp?cid=116
Blogs:
http://smsug.ca/blogs/garth_jones/default.aspx
http://myitforum.com/cs2/blogs/gjones/default.aspx

(in reply to erentz)

Post #: 4

RE: SMS Web Reporting Across Databases - 7/29/2008 1:54:20 PM

erentz

Posts: 6
Score: 0
Joined: 7/29/2008
Status: offline

I'm not sure I would care if guest or everyone could view the data in the external database. I'm guessing I'd have to enable the local guest account which I'm not allowed to do.

What account does SMS use for accessing the database when a web report is generated?

(in reply to gjones)

Post #: 5

RE: SMS Web Reporting Across Databases - 7/29/2008 2:17:30 PM

gjones

Posts: 844
Score: 50
Joined: 6/5/2001
From: Ottawa, Ontario, Canada
Status: offline

You don't have to enable guest on the server but yes you do need to enable guest on the db.

_____________________________

Garth@enhansoft.com

For a List of my Articles
http://www.myitforum.com/contrib/default.asp?cid=116
Blogs:
http://smsug.ca/blogs/garth_jones/default.aspx
http://myitforum.com/cs2/blogs/gjones/default.aspx

(in reply to erentz)

Post #: 6

RE: SMS Web Reporting Across Databases - 7/30/2008 8:35:42 AM

jschoone

Posts: 359
Score: 25
Joined: 4/10/2004
From: Norway
Status: offline

Hi!

Try John Nelsons blog where he describes use of linked servers in SQL (Use data outside the SMS DB in your SMS Web Report):
http://myitforum.com/cs2/blogs/jnelson/archive/2008/04/04/114622.aspx

Jan

(in reply to gjones)

Post #: 7

RE: SMS Web Reporting Across Databases - 7/30/2008 8:54:18 AM

gjones

Posts: 844
Score: 50
Joined: 6/5/2001
From: Ottawa, Ontario, Canada
Status: offline

The key to John's Blog post is the final paragraph about authentication, this might be an issue for allot of people.

_____________________________

Garth@enhansoft.com

For a List of my Articles
http://www.myitforum.com/contrib/default.asp?cid=116
Blogs:
http://smsug.ca/blogs/garth_jones/default.aspx
http://myitforum.com/cs2/blogs/gjones/default.aspx

(in reply to jschoone)

Post #: 8

RE: SMS Web Reporting Across Databases - 8/1/2008 2:44:57 PM

erentz

Posts: 6
Score: 0
Joined: 7/29/2008
Status: offline

Thanks, the linked server worked.

One thing I'm curious about. What security context/credentials does the SQL server use when using the linked server?

(in reply to gjones)

Post #: 9

RE: SMS Web Reporting Across Databases - 8/1/2008 5:43:38 PM

jnelson993

Posts: 925
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Well, it depends on what you set it to when you created the link...you can specify an explicit SQL login, or use the login's current security context (passthru), or have it made without using a security context. You specify that in the security tab when you create the new linked server.

If you followed the steps in my article, it uses passthru.

As for Garth's point, yeah, some people are going to get bit by the fact that they're trying to make multiple hops initiated on your own workstation. But if you kick all this stuff off from the SQL server you're linking FROM, it will cut down the hops to 1 again and all the authentication will pass through.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to erentz)

Post #: 10