Number2 Blog - myITforum

OVERVIEW

(note: this is written with SMS 2003 & SQL 2005 in mind because that's the environment I'm in right now, but I believe if the equivalent work was done in SCCM or in SQL 2000, the outcome would be the same...just a hunch) 

If you're like any normal SMS administrator, you've played around with SMS web reports and started getting some pretty useful data out of your SMS database.  The addition of web reports to SMS was quite a great addition because it really made rolling your own reports a LOT easier than doing custom ASP/ASP.NET for each bloomin' report or creating a new Excel spreadsheet everytime someone wanted different data.  If you're in the same boat as me, as soon as people at work start getting good SMS data out of SMS, they start asking crazy things like "Hey, can I get data from my timesheet application tied into a web report?  What about tying computer purchasing information from our ERP to the actual workstation data in SMS?".

I'm thinking: "Good God, man, I only get 5 hours of sleep a night as it is cuz this place has me wearing 12 hats and working all hours of the day and night; plus the cleanup work I've got to do on the existing reports and collections because out of the 30 SMS people on staff I seem to be the only one who knows how to write a query; plus someone in the office was patient 0 for some sort of midwestern SARS virus that's going around and I'm breaking ribs at night from the coughing; then there's the 5 kids under 9 years old I still have to help raise; not to mention the neighbor with a million birdfeeders seems to be attracting dozens of squirrels that use my roof as a highway and bury peanuts in my yard (not good for a household that has a 10month old with peanut allergies); did I mention the 17 year old neighbor boy who fixes cars until 1:00am and likes to rev the engines every 5 minutes so nobody gets any solid sleep?...but yeah, let me drop everything and add non-SMS related reports to my list of duties"

What I say is: "Hmm, I guess that might be possible, let me look into it." 

They say: "Great! (and then turning to coworker) Hey Bob, you know how we were wondering how we were going to create those reports?  This guy in IT said he'll figure it all out for us!" 

I'm thinking: "DOH!!!  Flippin'...frackin'...grumble...mumble...(and an aneurism ensues)..."

So now you HAVE to get non-SMS data into a web report...oooookaaaayyyy...

THE BASIC STEPS 

I've found a way to get non-SMS data into an SMS web report; there are likely several other ways to do it, using some of the steps outlined in this Microsoft article for example, but to me it looks pretty complicated and might require the use of extended stored procedures like xp_cmdshell that you just can't run in a super locked-down environment like ours because some security architects have deemed them potentially unsafe. Well, I don't like complicated and unsafe, that's why I broke up with the girl from the tatoo parlor, so here are the basic steps I'd use:

1. Create a linked server on the SMS database to the non-sms server telling it to use the logins' security context (pass-thru)
   
2. Create a stored procedure or table-valued function on your SMS server that queries the data on the remote DB using the linked server with ANSI_NULLS/WARNINGS turned on first.
3. Create an SMS web report with SQL code of EXEC stored_prodcedure_name in order to get the data into the report.

THE DETAILS

1) Create a linked server object on the SMS reporting server's SQL instance with the target being the remote server that holds the database you want to include in your report. Also configure the link to use the logon's current security context so user credentials are passed through.

A linked server object in SQL allows you to query data on a remote SQL server.  This is called a heterogeneous query or distributed query.  Once you've created a linked server definition, you can select from the remote server in SQL Management Studio by using the following syntax:

SNIPPET #1 - how to query a linked server object

SELECT column1, column2
FROM LinkedServer.database.schema.object

Here's some SQL code you can run on your SMS server to setup the linked server:

SNIPPET #2 - create linked server object from SMS server to non-SMS server

-- CREATE LINKED SERVER
EXEC sp_addlinkedserver
   @server = 'LINKEDSERVER' ,
   @srvproduct = 'MyProductName',
   @provider='SQLNCLI',
   @datasrc='LINKEDSERVER'--the data source

-- VALIDATE THAT IT CREATED THE LINK
select * from sys.servers where name='LINKEDSERVER'

-- MAKE LOGINS USE THEIR OWN CREDENTIALS ON REMOTE SERVER (pass-thru)
exec sp_addlinkedsrvlogin
   @RmtSrvName = 'LINKEDSERVER',
   @UseSelf = 'True'

-- VALIDATE THAT WORKED (if this is 1, it did)
select uses_self_credential as delegation
from sys.linked_logins as L, sys.servers as S
where S.server_id=L.server_id
  and S.name=N'LINKEDSERVER'

-- NOW TEST THE LINK
SELECT *
FROM LinkedServer.database.dbo._sp_Test
 

WHAT IF THE DB IS ON THE SAME SERVER AS SMS? 

If the external DB is on the same server as SMS, you still have to setup a linked server.  However, SQL won't let you create a linked server pointing to itself so you'll either have to use the IP address of the local machine instead of the computername, or perhaps you could try to create an alias in the HOSTS file, DNS and/or WINS that maps to the local IP address which should fool SQL into thinking the computer isn't really the local computer and allow it to link to itself.  I've tried using the IP address instead of the computername and that's worked; the others are just ideas you could try if you'd like.

2) In your reporting server's SMS database, create a stored procedure or table-valued function that queries the data on the remote DB using the linked server with ANSI_NULLS and ANSI_WARNINGS turned on ahead of time.

You would think that if you just put the select statement from snippet #1 into your report query it would work, but unfortunately it will just throw the following error:

The problem has to do with the connection from one server to the other requiring ANSI_NULLS and ANSI_WARNINGS set to ON for distributed queries.  Personally, I don't know how to set that ahead of time for a web-report connection, and no amount of messing with provider settings and the query code turned that on.  Perhaps I could have altered the DB and set those to on for the whole DB, but I don't know what the consequences of that are and didn't want to try it.  I'm open to ideas if anyone has any...but regardless, I wasn't able to figure out how to get those settings on for the connection even after asking some Microsoft folks so I gave up on trying that route. 

But it did get me thinking...why not create a stored procedure in my SMS db that queries that remote server but have ANSI_NULLS and ANSI_WARNINGS set on right before the stored procedure definition.  This way the stored procedure has them ON before even connecting to the remote server and solving said problem. So let's wrap our query above in a stored procedure:

SNIPPET #2 - Stored procedure on SMS server

USE [SMS_XXX]
GO
SET ANSI_NULLS ON --<--
GO
SET ANSI_WARNINGS ON --<--
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[_sp_Test]
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   SELECT column1, column2
   FROM [LinkedServer].database.schema.object
END

--GRANT RIGHTS FOR WEB REPORTING TO NEW PROC
GRANT EXECUTE ON dbo._sp_Test TO smsschm_users, webreport_approle

3) Create an SMS web report with the query being an EXEC command on the new stored procedure

The creation of SMS web reports is outside the scope of this document, but essentially when you create one, instead of some big SELECT statement you would type:

SNIPPET #3 - Web report SQL

EXEC dbo._sp_Test 

Now when you run the report, data from your remote, non-SMS server will show up in the report!

SUMMARY AND NEXT STEPS

In this article we learned the steps involved in querying non-sms data into an SMS web report are

1. Create a linked server on the SMS database to the non-sms server telling it to use the logins' security context
   Get SQL code here --> CreateLinkedServer.txt
2. Create a stored procedure or table-valued function that queries the data on the remote DB using the linked server with ANSI_NULLS/WARNINGS turned on first.
   Get SQL code here --> CreatedLinkedStoredProc.txt
   
3. Create an SMS web report with SQL code of EXEC stored_prodcedure_name in order to get the data into the report.

Now, it's up to you to write your own select statement to pull meaningful data.  (BTW, to work with data that comes back from a stored procedure, you must first create a temp table and insert it into said temp table so you may select & filter on that.

Also, if you have kerberos all set up and you're a pretty secure shop, you may find that if you go to manually create a linked server from your own workstation that's connected to the SMS box, it won't pass through your credentials and uses ANONYMOUS instead so the link doesn't get created.  This is because you've now got 2 hops from the client to the end.  One hop gets you from your workstation to the SMS server and another hop gets you from the SMS server to the target server.  Out of the box, SQL can't pass credentials over 2 hops. If it uses NTLM authentication, NTLM doesn't support 2+ hops so you're out of luck.  If it uses Kerberos authentication, Kerberos doesn't pass your Kerberos ticket on to the next hop unless you've got delegation turned on (see the "Security Account Delegation" topic in SQL Server Books Online).

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