May 22, 2013 at 9:54 pm
To my friends in the community, here is a stumper that I can't seem to find sufficient information on despite repeated Google searches to figure out how to solve. Let me preface my explanation by saying that I am not a DBA and I have only limited access to the involved servers because they belong to a customer for which we are implementing an application.
What is going to be needed is kind of a step-by-step, cookbook approach because I'm not sure at quite what level their DBA resources operate. Without further adieu:
1. Our application runs on a virtualized server named SQL2. I do not have direct access to this server but I can access the database for our application using SSMS from the application tier's server.
2. There is a SQL Server Agent job defined that runs on SQL2 but links to a different SQL Server (SQL1) containing another application from which our application needs to draw some data. I do have limited access directly to SQL1 but very limited privileges there.
3. On both servers, there is a SQL account defined ('myaccount') and the password is the same on both. This account does have sufficient privs on SQL1 to access the customer's application's database, or at least the table I need to read from.
On my application server, I can launch SSMS, connect to SQL2 and run the SP below and it runs just fine. It is but a small subset of the actual SP that needs to run to keep this somewhat simple. I have defined an agent job on SQL2 whose owner is 'myaccount.' When the agent job runs it gets an error message. It is set up to run the exact same EXEC as shown below.
-- From SSMS this runs without issues:
EXEC [dbo].[TestConnectToLinkedServer];
-- Above is defined as the job step in the agent job named "Test Linked Server Connection"
-- When I run this, it fails
EXEC msdb..sp_start_job N'Test Linked Server Connection';
-- I can get the error message it fails on with this (top 2 rows):
EXEC dbo.sp_help_jobhistory
@job_name = N'Test Linked Server Connection',@mode='FULL' ;
The error message returned by the last query is:
The job failed. The Job was invoked by User myaccount. The last step to run was step 1 (Run SP).
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed.
Clearly the account that is being used on SQL1 (AUTHORITY\ANONYMOUS LOGON) isn't the same as the one on SQL2 (NT AUTHORITY\SYSTEM). I'm thinking this must have something to do with the account mappings but I am not sure on which server the mapping needs to be changed (or for that matter what it needs to be changed to).
Here is the SP that is run either manually or by the agent:
CREATE PROCEDURE [dbo].[TestConnectToLinkedServer]
AS BEGIN
DECLARE @CustAppDBname NVARCHAR(20) = N'CustApp'
,@LinkedServerNVARCHAR(20)= N'SQL1'
,@retvalINT
,@ErrorCountINT = 0
,@ErrorCount1INT = 0
,@SQLNVARCHAR(MAX);
IF NOT EXISTS (
SELECT 1
FROM sys.servers
WHERE name = @LinkedServer)
EXEC sp_addlinkedserver @server=@LinkedServer, @srvproduct=N'SQL Server';
BEGIN TRY
-- This will fail if there is no server to link to (i.e., account not recognized)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@LinkedServer
,@useself='TRUE', @locallogin='myaccount';
SELECT @retval = 0;
END TRY
BEGIN CATCH
SELECT -3, 'Problem linking to Customer'' app server: ' + @LinkedServer, @retval
,[SQL Error Number] = ERROR_NUMBER()
,[SQL Error Severity] = ERROR_SEVERITY()
,[SQL Error State] = ERROR_STATE()
,[SQL Error Procedure] = ERROR_PROCEDURE()
,[SQL Error Line] = ERROR_LINE()
,[SQL Error Message] = ERROR_MESSAGE();;
SELECT @ErrorCount = @ErrorCount + 1, @retval = -1;
SELECT @ErrorCount1 = -3
END CATCH
IF @ErrorCount = 0
EXEC @retval = sys.sp_testlinkedserver @LinkedServer;
IF @retval <> 0
BEGIN
SELECT -4, 'Test of linked server failed'
SELECT @ErrorCount = @ErrorCount + 1, @retval = -1;
END
SELECT @SQL = N'
SELECT TOP 10 * FROM [' + @LinkedServer + N'].[' + @CustAppDBname
+ N'].dbo.AppTable'
EXEC sp_executesql @SQL
IF @LinkedServer IS NOT NULL AND EXISTS (
SELECT 1
FROM sys.servers
WHERE name = @LinkedServer)
BEGIN
IF @ErrorCount1 <> -3
EXEC sp_droplinkedsrvlogin @rmtsrvname=@LinkedServer, @locallogin='myaccount';
EXEC sp_dropserver @LinkedServer, 'droplogins';
END
END
Any help would be greatly appreciated.
BTW. I am not sure there's sufficient information here to lead someone to identify and precisely resolve the issue. If there is not, please let me know what additional information you need to diagnose the issue and I'll try to get it. It may take a day or two to get it as I'll be relying on others arms and legs to get it done (most likely).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 24, 2013 at 4:17 pm
I think this has to do with the fact that the SQL Agent Service on SQL2 is running under the NT AUTHORITY\SYSTEM account, which is a local account. You should be able to configure the SQL Agent Service to use an appropriate domain account in the SQL Server Configuration Manager:
http://msdn.microsoft.com/en-us/library/ms191543(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms186264(v=sql.105).aspx
Jason Wolfkill
May 24, 2013 at 7:26 pm
Thanks Wolf Kill!
This sounds promising and I'll look into it on Monday.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 28, 2013 at 2:27 am
wolfkillj (5/24/2013)
I think this has to do with the fact that the SQL Agent Service on SQL2 is running under the NT AUTHORITY\SYSTEM account, which is a local account. You should be able to configure the SQL Agent Service to use an appropriate domain account in the SQL Server Configuration Manager:http://msdn.microsoft.com/en-us/library/ms191543(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms186264(v=sql.105).aspx
As I suspected I don't have the access to directly apply the information in these links.
But I have asked the client to take some screen shots and send them to me. Hopefully something will jump out at me from that.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 2, 2013 at 6:45 pm
Hi again Wolf Kill.
I just wanted you to know that I am not trying to be rude by not letting you know if your suggestion worked.
I sent some suggested steps to the client to execute one week ago and still have not heard back.
I will post ultimately if I find the solution (or if I simply have some more questions).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 3, 2013 at 4:54 am
dwain.c (6/2/2013)
Hi again Wolf Kill.I just wanted you to know that I am not trying to be rude by not letting you know if your suggestion worked.
I sent some suggested steps to the client to execute one week ago and still have not heard back.
I will post ultimately if I find the solution (or if I simply have some more questions).
No worries - I'm sure it's frustrating to have a suggested solution that you can't try without waiting for the client to implement it!
Jason Wolfkill
June 3, 2013 at 6:30 pm
wolfkillj (6/3/2013)
dwain.c (6/2/2013)
Hi again Wolf Kill.I just wanted you to know that I am not trying to be rude by not letting you know if your suggestion worked.
I sent some suggested steps to the client to execute one week ago and still have not heard back.
I will post ultimately if I find the solution (or if I simply have some more questions).
No worries - I'm sure it's frustrating to have a suggested solution that you can't try without waiting for the client to implement it!
Indeed it is. But yesterday they did send me some screen shots that has led me to suggest a couple of possible courses of action. I may know within a day or so whether one of them solves the problem.
Thanks again.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 5, 2013 at 12:23 am
Wolf Kill - If you're still listening I need some further advice.
I was able to get the thing to work by changing this line in the SP:
,@useself='TRUE', @locallogin='myaccount';
To something like:
,@useself='FALSE', @rmtuser = 'myaccount', @rmtpassword = 'mypassword';
This works using the SQL Config Manager and setting the Log On/Built In Account to either Local System or Network Service.
When I look at using Log On/This Account, trying to browse the available accounts, I do not find 'myaccount' in the list. I don't like the idea of hard-coding the login password to SQL1 in my SP. And I'm not sure how to get SQL Agent to run as myaccount instead of NT AUTHORITY/SYSTEM (which is apparently what it runs as when you select Local System as the built in account).
Is there some permission grant required to be applied to myaccount that would make it available as an account under which SQL Agent can log on as?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 5, 2013 at 1:02 am
Further poking around leads me to believe that the only accounts listed by the Config Mgr are Windows authenticated accounts, whereas 'myaccount' is a SQL authenticated account.
I'm wondering if it might be possible to create equivalent Windows user accounts on SQL1 and SQL2, add those accounts to SQL Server as SQL1\myaccount and SQL2\myaccount, set the server roles properly and the change SQL Agent on SQ2 to use SQL2\myaccount.
Would then it be possible to @useself='TRUE'?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 5, 2013 at 4:44 am
dwain.c (6/5/2013)
Further poking around leads me to believe that the only accounts listed by the Config Mgr are Windows authenticated accounts, whereas 'myaccount' is a SQL authenticated account.I'm wondering if it might be possible to create equivalent Windows user accounts on SQL1 and SQL2, add those accounts to SQL Server as SQL1\myaccount and SQL2\myaccount, set the server roles properly and the change SQL Agent on SQ2 to use SQL2\myaccount.
Would then it be possible to @useself='TRUE'?
hi Dwain,
Its a DBA's role to give you the right permission for this one. You should request for the service accounts that suits your application needs. Service Account should be a member of sysadmin role to play with the jobs.
this article shows how to add none administrators of the server to run the sql server agent.
I hope this helps.
===============================================================
"lets do amazing" our company motto..
June 5, 2013 at 6:51 am
dwain.c (6/5/2013)
Further poking around leads me to believe that the only accounts listed by the Config Mgr are Windows authenticated accounts, whereas 'myaccount' is a SQL authenticated account.I'm wondering if it might be possible to create equivalent Windows user accounts on SQL1 and SQL2, add those accounts to SQL Server as SQL1\myaccount and SQL2\myaccount, set the server roles properly and the change SQL Agent on SQ2 to use SQL2\myaccount.
Would then it be possible to @useself='TRUE'?
Once again, I'm not at a machine that I can use to verify this for certain, but I'm not sure if using equivalent local accounts would work. You could try it and see, of course. Where I work, we have AD domain accounts that are specifically for SQL Agent services. We have one for each environment - DEV, QA, etc. They're granted all the necessary permissions on each server and SQL Server instance so they can log in to the right boxes and instances, access file system locations, etc.
Jason Wolfkill
June 5, 2013 at 7:03 am
dwain.c (6/5/2013)
Further poking around leads me to believe that the only accounts listed by the Config Mgr are Windows authenticated accounts, whereas 'myaccount' is a SQL authenticated account.I'm wondering if it might be possible to create equivalent Windows user accounts on SQL1 and SQL2, add those accounts to SQL Server as SQL1\myaccount and SQL2\myaccount, set the server roles properly and the change SQL Agent on SQ2 to use SQL2\myaccount.
Would then it be possible to @useself='TRUE'?
Oh, and I'm pretty sure you're right that Config Mgr only shows Windows accounts. If you select an appropriate account, "@useself= TRUE" should work. "Self" will refer to the account used by the SQL Agent service in that context, so you'll have to be sure that account is set as the proxy used by any other calls to the linked server the SP creates.
Jason Wolfkill
June 5, 2013 at 5:40 pm
Thanks for the replies.
I have come to the awkward conclustion that the client has no DBA. I do believe they have some folks that are familiar with Windows domain administration, so I am hopeful once I'm on site (17-21 Jun) I can work with one of those people to set it up, based on the information provided and what I've read.
I will most certainly post my solution once I figure it out.
In the meantime, I have an idea of how I can use the rmtuser/rmtpassword and store the password encrypted somewhere which will provide a somewhat suitable workaround if for some reason I can't get it to work.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 2, 2013 at 12:37 am
dwain.c (6/5/2013)
Wolf Kill - If you're still listening I need some further advice.I was able to get the thing to work by changing this line in the SP:
,@useself='TRUE', @locallogin='myaccount';
To something like:
,@useself='FALSE', @rmtuser = 'myaccount', @rmtpassword = 'mypassword';
This works using the SQL Config Manager and setting the Log On/Built In Account to either Local System or Network Service.
When I look at using Log On/This Account, trying to browse the available accounts, I do not find 'myaccount' in the list. I don't like the idea of hard-coding the login password to SQL1 in my SP. And I'm not sure how to get SQL Agent to run as myaccount instead of NT AUTHORITY/SYSTEM (which is apparently what it runs as when you select Local System as the built in account).
Is there some permission grant required to be applied to myaccount that would make it available as an account under which SQL Agent can log on as?
I did promise to post the final resolution to this issue. I ended up using the above approach (@useself='FALSE') while specifying the SQL login creds when adding the linked server account. I went to the lengths of storing the user account and encrypted password (MD5 hash) in the database and then using a rather obscure SQL syntax to retrieve them.
While not optimal, the customer was satisfied that as long as the password wasn't stored in clear text in the database, they could live with it.
Unfortunately, the alternative of getting them to resolve the authentication when @useself='TRUE' was probably going to be just too much for them.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 2, 2013 at 6:16 am
dwain.c (7/2/2013)
dwain.c (6/5/2013)
Wolf Kill - If you're still listening I need some further advice.I was able to get the thing to work by changing this line in the SP:
,@useself='TRUE', @locallogin='myaccount';
To something like:
,@useself='FALSE', @rmtuser = 'myaccount', @rmtpassword = 'mypassword';
This works using the SQL Config Manager and setting the Log On/Built In Account to either Local System or Network Service.
When I look at using Log On/This Account, trying to browse the available accounts, I do not find 'myaccount' in the list. I don't like the idea of hard-coding the login password to SQL1 in my SP. And I'm not sure how to get SQL Agent to run as myaccount instead of NT AUTHORITY/SYSTEM (which is apparently what it runs as when you select Local System as the built in account).
Is there some permission grant required to be applied to myaccount that would make it available as an account under which SQL Agent can log on as?
I did promise to post the final resolution to this issue. I ended up using the above approach (@useself='FALSE') while specifying the SQL login creds when adding the linked server account. I went to the lengths of storing the user account and encrypted password (MD5 hash) in the database and then using a rather obscure SQL syntax to retrieve them.
While not optimal, the customer was satisfied that as long as the password wasn't stored in clear text in the database, they could live with it.
Unfortunately, the alternative of getting them to resolve the authentication when @useself='TRUE' was probably going to be just too much for them.
Thanks for letting us know how it worked out, Dwain. Sounds like you did the best you could in a tough situation and did a good job of fully informing the client of the downside of the solution.
Jason Wolfkill
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply