October 2, 2012 at 12:32 pm
Hello,
You must feel the question's title is strange, but I am not kidding here.
I have a website which is on my development laptop, SQL server is also on the laptop, XP+SQL 2005, everything work problem free.
I am in the middle of migrating it over to new environment: Windows 2008 R2 (IIS server) + SQL 2008 R2 (SQL server), these two servers are on different machines.
My configuration is to use Windows Authentication and a special identity to run the application pool where the website resides on the IIS server.
All pages work fine except a page using ComponentArt's chart, yes, only chart, even for other ComponentArt's products, there are no problems.
The error thrown by the page is something leading both me and CA support to think that data is not retrieved and the support just wants me to find out and confirm the data is retrieved.
The special identity is a domain id, it has been created as a login on SQL server so all my SPs in my website are using it to communicate with SQL database.
Here is what I have tried in my troubleshooting:
1. On the SQL server, I tested and confirmed the stored procedure (which is used by the page) can get the data for chart
2. I started a profiler on SQL, and tried to open the page from IIS server
3. I can see the profiler caught the request and executed the stored procedure
4. From profiler, I don't see difference between running the sp locally or remotely
I do doubt there is some problem with the id though, because I also try to confirm that I am using the right identity to run sp locally and I don't know how? I connected to SQL using my own id (which is a dbo id)
Like I said, the special id is a domain id, not a SQL id with username/password
How can I confirm that the IIS "indeed is using this special account" to talk to SQL and get data? I know from SQL profiler it looks YES, but where can I see the data it returns in profiler? or somewhere else? how?
Thank you.
October 2, 2012 at 12:38 pm
Got a new finding here:
I tried to run the sp as the identity:
EXECUTE AS user = 'testbns\AOR'
EXECUTE spDashboardByCategoryID 1
REVERT
It DOESN'T return any data and no any error message.
I tried to track the execution procedure so I put print '-------------------' at the begining of the sp, the EXECUTE AS still doesn't show any message to me.
but if I just run EXECUTE spDashboardByCategoryID 1, I get the right data.
WHY?
October 2, 2012 at 1:24 pm
Here is my new finding:
I started profiler, I got different trace for
EXECUTE AS user = 'testbns\AOR'
EXECUTE spDashboardByCategoryID 1
REVERT
GO
EXECUTE spDashboardByCategoryID 1
Please see the attached screenshot: the top line and the bottom line are for the different calls.
October 2, 2012 at 2:44 pm
I fixed the problem, caused by a missing privilege grant on one table that the sp is refering to.
Thanks for all viewers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply