xp_cmdshell and TableDiff.exe
-
I have an interesting question to pose:
I have SS2K8 installed on my laptop, and I have a sandbox with 2005, and I have a QA box that is 2000, so we have all flavors of SQL involved here, and I am local admin on all boxes.
First, I have a script that will use xp_cmdshell (turning it on prior to and off after) to execute a tablediff quick comparison. My destination machine is always 2000. I am not using login/pwds in the TableDiff parameters.
My Laptop (2k8) was my first source. When I ran the xp_cmdshell version of the tablediff, I was able to connect to my local source, but not the 2k destination. The error I get is that is "Unable to connect to database <destinationdatabase>". I was connected to my network via VPN, so I was not sure if my Trusted Connection was truly trusted. I do not know if a trusted connection requires Kerberos to authenticate. So my next step was to use a cmd prompt, and run the TableDiff.exe with the same parameters there. It worked.
My sandbox (2k5) was my next source. Again, when I ran the xp_cmdshell version of the tablediff, I was able to connect to my local source, but not the 2k destination. It was the same exact error message that I got in 2k8. The difference here was that I connected to my sandbox via RDP, which eliminates the VPN element, and provides a true Trusted Connection. Again, I pulled the code out of the xp_cmdshell statement, verbatim, and ran it in a command prompt window. It worked fine.
My question is, why can I not connect to the destination database when I am running the executable via T-SQL as opposed to a cmd prompt? Is there additional validation or authentication that gets in the way when executing the T-SQL? I tried to make it backwards-compatible. It wouldn't make sense to run it in 2k against a 2k8 destination, in other words. Am I missing something here?
Karl Lambert
SQL Server Database Administration
Business Intelligence Development -
xp_cmdShell does not use your credentials.
this is a common security misconception . The problem is that when you access any resource OUTSIDE of SQL server, like network shares, local hard drives,xp_cmdshell,sp_OA type functions etc, it doesn't matter what YOUR credentials are, like Domain Admin,Local Admin etc, because SQL will not carry those credentials to the "outside of SQL" security context.
SQL Server uses the account it starts with to try and access the resource:
That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.
As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.
Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.
you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the linked server works when SQL is run your credentials, so you'd know you need a domain account to access the resource.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you for your reply, Lowell. What you describe in your post makes perfect sense. My services are running as Local System on both source machines. I will make the change to use my network credentials and test again. I will post the results.
Karl Lambert
SQL Server Database Administration
Business Intelligence Development -
Karl in your case, since Tablediff takes username/password parameters for the servers it will compare, you could just include those and not have to fiddle with the startup accounts, but keep that security gotcha in mind for other things you try to access.
on a side note, for the CFL in for CFL_DBA, is that Central Florida, Canadian Football League, or what?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
These are my machines that I use to test, develop, etc., so changing the service account isnt a big deal. We have designated service accounts in our Production environments, so this same issue does not arrise there (thank goodness!)
CFL stands for Central Florida.
Karl Lambert
SQL Server Database Administration
Business Intelligence Development -
Sorry for the delay in this reply...
I changed the account my SQL Server service uses to logon to use my network ID, restarted the service, and the TableDiff worked! I appreciate the info, Lowell. Thanks!
Karl Lambert
SQL Server Database Administration
Business Intelligence Development
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply