May 31, 2010 at 2:06 pm
Hi folks,
I am wondering if it is possible to execute a sp or fn on a linked server with a read-access account but execute it as another account (not knowing the password)? It seems to me like a security security issue and should not be allowed. Real scenario is: if my developer has read access to a production server, and he knows the name of the powerful account and the account is used to run all the stored procedures, for the convenience of troubleshooting, if he can execute as that account remotely, he might use that as a back hole.
Is this possible? if yes, how do I prevent that?
Thanks lots in advance.
May 31, 2010 at 10:43 pm
halifaxdal (5/31/2010)
execute a sp or fn on a linked server with a read-access account but execute it as another account (not knowing the password)?
i dont think it can be done .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 1, 2010 at 8:54 am
To use "Execute AS," I'm pretty sure you need Sysadmin access or you need to be executing a proc that has that phrase *in* the code.
If you only have read access to the server (regardless of if it's Linked Server access or direct server access), and you do not have EXECUTE perms on the schema or on the procs, then you simply cannot execute any procs. And you can't impersonate another account, either.
Create yourself a test account on a Dev box. Give that test account the same perms as the developer in question. Open another instance of SSMS, logging in as that test account, then try to use Execute AS. You'll see exactly what I'm talking about.
June 1, 2010 at 11:17 am
It is possible to run query at a linked server as another user with the EXECUTE AS statement. In the example below I login as user "Test" and I execute a query on a linked server as "SERVER02_User".
--Create linked server
EXEC master.dbo.sp_addlinkedserver
@server = N'SERVER01',
@srvproduct=N'SQL Server'
--Add linked server login
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'SERVER01',
@useself=N'False',
@locallogin=N'SERVER02_User',
@rmtuser=N'SERVER01_User',
@rmtpassword='password'
With this linked server I can execute a query as the user "SERVER02_User"
--Change login
EXECUTE AS LOGIN='SERVER02_User'
--Execute query as 'SERVER02_User'
EXEC sp_executesql N'SELECT * FROM SERVER01.test.dbo.sysobjects'
--Revert back to your previous user
REVERT
Until now it's only possible for me to do this as a SQL user. Unfortunatly I haven't succeeded in doing this with Windows Authentication. I'm trying to find a solution with Microsoft Premier Support, but untill now we haven't been succesfull.
See the link below for the complete story:
http://www.sqlservercentral.com/Forums/Topic892098-359-1.aspx#bm892768
Regards,
Marco
June 1, 2010 at 11:21 am
mchofman (6/1/2010)
It is possible to run query at a linked server as another user with the EXECUTE AS statement. In the example below I login as user "Test" and I execute a query on a linked server as "SERVER02_User".
Marco, what permissions does Test have? What permissions do the other two users (the ones used in Linked Server) have?
And you said it Test was a SQL only account? What about the other two? SQL logins?
June 1, 2010 at 11:32 am
I don't know what settings Marco used, but I created a Test sql account on a server, using Master as the default and granted no permissions.
The linked server does not impersonate an account (like Marco's LS does) or have any local to remote login mappings. It uses a specific security context login (bottom of the LS GUI page -> Security tab) to log into the other server.
Using this as plain vanilla (no Execute AS), I can remote over to the other server and read sys.objects just like Marco seems to be able to. Trying to use the Execute AS code, though, to use a db_owner sql-only account on the other end, though, fails with the following error:
Cannot execute as the server principal because the principal "ThisLogin" does not exist, this type of principal cannot be impersonated, or you do not have permission.
So it looks like the question is all about how Marco's accounts are set up. Probably the fact that he has the impersonate ability turned on is what's giving him perms and why I've never seen anything but an error message. I never use impersonation in my linked servers.
June 1, 2010 at 11:50 am
I have two servers:
- SERVER01
- SERVER02
- On SERVER01 I create a SQL user named "SERVER01_User" with access to the table "SERVER01.test.dbo.sysobjects"
- On SERVER02 I create a SQL user name "SERVER02_User"
- Then I create the linked server on SERVER02. This linked server maps local user "SERVER02_User" to the remote user "SERVER01_User"
- After that I can run my query while being connected as user Test (which doesn't have special permissions)
Regards,
Marco
June 2, 2010 at 8:12 am
So I guess the answer depends on how the linked server is setup. I'm betting the impersonate permission is causing your ability to use it.
June 2, 2010 at 8:49 am
I don't use impersonate. When I look on the security tab of my linked server this option is not marked.
You can check which user you are by running te command "SELECT suser_name()"
SELECT suser_name()
EXECUTE AS LOGIN='SERVER02_User'
SELECT suser_name()
EXEC sp_executesql N'SELECT * FROM SERVER01.test.dbo.sysobjects'
REVERT
SELECT suser_name()
In the SQL Profiler on the remote server you can check which user logs on. This should be "SERVER01_User"
Regards,
Marco
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply