June 8, 2018 at 9:53 am
I spent much of yesterday trying to figure out how to use the undocumented procedure xp_fileexist with a non sys admin SQL Server account to no avail. The procedure works as expected when run by an account that is a member of the sysadmin fixed server role. I have found a way to use it but I am not happy with way I would have to implement it in a production environment as that would entail granting the lessor privileged account the ability to impersonate a sysadmin account.
So far I have been unable to find another way nor have I been able to find what permissions in the sysadmin fixed server role makes this work.
With some suggestions from Jeff Modem (thank you, sir) I have tried setting up the proxy account for xp_cmdshell, but that hasn't helped either.
If anyone has any ideas, I am all ears.
June 8, 2018 at 10:14 am
I'm presuming when you run it as a non-sysadmin, it throws an error of some sort?
What I might do is, because it's trying to hit the file system, is try to run it with a non-sysadmin account, then check the OS Security and Application event logs to see what, if anything, got recorded. You might get something there to point you in the direction of the problem.
Of course, that presumes that when you run it as a non-sysadmin, it's not just an outright "you don't have sufficient privileges to execute" sort of message.
June 8, 2018 at 10:45 am
Crud. Sorry Lynn... I got busy last night and forgot.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2018 at 10:53 am
jasona.work - Friday, June 8, 2018 10:14 AMI'm presuming when you run it as a non-sysadmin, it throws an error of some sort?
What I might do is, because it's trying to hit the file system, is try to run it with a non-sysadmin account, then check the OS Security and Application event logs to see what, if anything, got recorded. You might get something there to point you in the direction of the problem.Of course, that presumes that when you run it as a non-sysadmin, it's not just an outright "you don't have sufficient privileges to execute" sort of message.
Actually, it runs without noticeable errors, just returns all 0's. I will have to check the error logs now.
June 8, 2018 at 11:00 am
Lynn Pettis - Friday, June 8, 2018 10:53 AMjasona.work - Friday, June 8, 2018 10:14 AMI'm presuming when you run it as a non-sysadmin, it throws an error of some sort?
What I might do is, because it's trying to hit the file system, is try to run it with a non-sysadmin account, then check the OS Security and Application event logs to see what, if anything, got recorded. You might get something there to point you in the direction of the problem.Of course, that presumes that when you run it as a non-sysadmin, it's not just an outright "you don't have sufficient privileges to execute" sort of message.
Actually, it runs without noticeable errors, just returns all 0's. I will have to check the error logs now.
Does sql server (or the proxy) have the privs to see the target directory?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2018 at 11:08 am
Lynn Pettis - Friday, June 8, 2018 10:53 AMjasona.work - Friday, June 8, 2018 10:14 AMI'm presuming when you run it as a non-sysadmin, it throws an error of some sort?
What I might do is, because it's trying to hit the file system, is try to run it with a non-sysadmin account, then check the OS Security and Application event logs to see what, if anything, got recorded. You might get something there to point you in the direction of the problem.Of course, that presumes that when you run it as a non-sysadmin, it's not just an outright "you don't have sufficient privileges to execute" sort of message.
Actually, it runs without noticeable errors, just returns all 0's. I will have to check the error logs now.
OK, after some quick testing, I'm thinking maybe it is going to be a file-system permission issue. I just ran the XP with a non-SA account (but a domain account, not a SQL Login) on a folder / file on my server and got back 1/0/1 for the results. Now, my non-SA account is unable to log into the server, but does belong to the Domain Users group, which likely has at least some permissions to the folder / file I checked.
Short of logging into the server, though, I can't say for sure.
June 8, 2018 at 11:25 am
Lynn Pettis - Friday, June 8, 2018 9:53 AMI spent much of yesterday trying to figure out how to use the undocumented procedure xp_fileexist with a non sys admin SQL Server account to no avail. The procedure works as expected when run by an account that is a member of the sysadmin fixed server role. I have found a way to use it but I am not happy with way I would have to implement it in a production environment as that would entail granting the lessor privileged account the ability to impersonate a sysadmin account.So far I have been unable to find another way nor have I been able to find what permissions in the sysadmin fixed server role makes this work.
With some suggestions from Jeff Modem (thank you, sir) I have tried setting up the proxy account for xp_cmdshell, but that hasn't helped either.
If anyone has any ideas, I am all ears.
I got one to work as well but I needed the login that is mapped to a certificate in the sysadmins group. Similar to what you saw. Not sure if it's much better although you can limit it. A bit rough and ugly but this is what I threw together:
CREATE DATABASE SomeDatabase
GO
CREATE LOGIN SomeLogin WITH PASSWORD=N'someconvolutedpassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE SomeDatabase
GO
CREATE USER SomeLogin FOR LOGIN SomeLogin
GO
--Stored procedure to call xp_fileexists
CREATE PROCEDURE dbo.NewXP_fileexist
@FileName varchar(255)
AS
EXEC xp_fileexist @FileName
RETURN
GO
--test with my account to make sure it works
EXEC NewXP_fileexist 'C:\Temp\Test.xls'
--need to grant execute on the stored procedure to the user
GRANT EXECUTE ON dbo.NewXP_fileexist TO SomeLogin
--create cert in master so it can be mapped to a login
USE master
CREATE CERTIFICATE ExecuteXPCert
ENCRYPTION BY PASSWORD = 'mibT4a$pwd2'
WITH SUBJECT = 'Certificate for signing extended stored procedures'
GO
--create login from certificate, grant view server state
CREATE LOGIN ExecuteXP FROM CERTIFICATE ExecuteXPCert
GO
--only sysadmins can execute, add login from cert
ALTER SERVER ROLE sysadmin ADD MEMBER ExecuteXP
GO
-- backup cert so you can create it from file in the destination database
BACKUP CERTIFICATE ExecuteXPCert
TO FILE='C:\temp\ExecuteXPCert.cer'
WITH PRIVATE KEY
( FILE = 'C:\temp\ExecuteXPCert.pvk',
-- encryption key password
DECRYPTION BY PASSWORD = 'mibT4a$pwd2',
--new pwd for encrypted file,
--delete files when done in destination database
ENCRYPTION BY PASSWORD = 'yoqt&f3pD'
)
GO
--Use destination database with SP, create cert from file
USE SomeDatabase
GO
CREATE CERTIFICATE ExecuteXPCert
FROM FILE='C:\temp\ExecuteXPCert.cer'
WITH PRIVATE KEY
( FILE = 'C:\temp\ExecuteXPCert.pvk',
--file password
DECRYPTION BY PASSWORD = 'yoqt&f3pD',
--encryption key password
ENCRYPTION BY PASSWORD = 'mibT4a$pwd2'
)
GO
--***DELETE CERT FILES from C:\temp after creating in destination database above
--Sign the sp so it runs under the login mapped to the certificate
ADD SIGNATURE TO OBJECT::dbo.NewXP_fileexist
BY CERTIFICATE ExecuteXPCert
WITH PASSWORD='mibT4a$pwd2'
GO
--Test
EXECUTE AS LOGIN='SomeLogin'
EXEC dbo.NewXP_fileexist 'C:\Temp\Test.xls'
REVERT
GO
Sue
June 8, 2018 at 11:32 am
I'm at work and don't have the time to test right now nor the code that I used previously but you don't actually need a certificate for this type of thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2018 at 1:28 pm
Jeff Moden - Friday, June 8, 2018 11:32 AMI'm at work and don't have the time to test right now nor the code that I used previously but you don't actually need a certificate for this type of thing.
Nope but I generally do the certificate signing with stored procedures so that's why I used that one.
Sue
June 8, 2018 at 1:55 pm
This would be the other method with execute as owner: USE SomeDatabase
GO
--sysadmin needs to create
CREATE PROCEDURE RunExtendedProc
@FileName varchar(255)
WITH EXECUTE AS OWNER
AS
EXEC xp_fileexist @FileName
RETURN
GO
GRANT EXECUTE on RunExtendedProc to SomeLogin
EXECUTE AS LOGIN = 'SomeLogin'
EXEC dbo.RunExtendedProc 'C:\Temp\Test.xls'
June 8, 2018 at 2:06 pm
Sue_H - Friday, June 8, 2018 1:55 PMThis would be the other method with execute as owner:
USE SomeDatabase
GO
--sysadmin needs to create
CREATE PROCEDURE RunExtendedProc
@FileName varchar(255)
WITH EXECUTE AS OWNER
AS
EXEC xp_fileexist @FileName
RETURN
GOGRANT EXECUTE on RunExtendedProc to SomeLogin
EXECUTE AS LOGIN = 'SomeLogin'
EXEC dbo.RunExtendedProc 'C:\Temp\Test.xls'
For that to work, at least based on my testing, that the original user must be able to impersonate the user the procedure is running under.
June 8, 2018 at 2:34 pm
Lynn Pettis - Friday, June 8, 2018 2:06 PMSue_H - Friday, June 8, 2018 1:55 PMThis would be the other method with execute as owner:
USE SomeDatabase
GO
--sysadmin needs to create
CREATE PROCEDURE RunExtendedProc
@FileName varchar(255)
WITH EXECUTE AS OWNER
AS
EXEC xp_fileexist @FileName
RETURN
GOGRANT EXECUTE on RunExtendedProc to SomeLogin
EXECUTE AS LOGIN = 'SomeLogin'
EXEC dbo.RunExtendedProc 'C:\Temp\Test.xls'For that to work, at least based on my testing, that the original user must be able to impersonate the user the procedure is running under.
Those can get flakey with SQL Logins - I think other than just orphans so I just created and tested with a Windows login with no permissions other than mapped to database and execute the stored proc. No error executing as that account and just logged into SSMS as the Windows user and it was fine as well.
I've had that error before when I used a member of an AD group and the user isn't explicitly granted access with their individual login. I can't remember the others.
Sue
June 8, 2018 at 2:41 pm
Sue_H - Friday, June 8, 2018 2:34 PMLynn Pettis - Friday, June 8, 2018 2:06 PMSue_H - Friday, June 8, 2018 1:55 PMThis would be the other method with execute as owner:
USE SomeDatabase
GO
--sysadmin needs to create
CREATE PROCEDURE RunExtendedProc
@FileName varchar(255)
WITH EXECUTE AS OWNER
AS
EXEC xp_fileexist @FileName
RETURN
GOGRANT EXECUTE on RunExtendedProc to SomeLogin
EXECUTE AS LOGIN = 'SomeLogin'
EXEC dbo.RunExtendedProc 'C:\Temp\Test.xls'For that to work, at least based on my testing, that the original user must be able to impersonate the user the procedure is running under.
Those can get flakey with SQL Logins - I think other than just orphans so I just created and tested with a Windows login with no permissions other than mapped to database and execute the stored proc. No error executing as that account and just logged into SSMS as the Windows user and it was fine as well.
I've had that error before when I used a member of an AD group and the user isn't explicitly granted access with their individual login. I can't remember the others.Sue
Have to use SQL logins for this app.
June 8, 2018 at 3:27 pm
Lynn Pettis - Friday, June 8, 2018 2:41 PMSue_H - Friday, June 8, 2018 2:34 PMLynn Pettis - Friday, June 8, 2018 2:06 PMSue_H - Friday, June 8, 2018 1:55 PMThis would be the other method with execute as owner:
USE SomeDatabase
GO
--sysadmin needs to create
CREATE PROCEDURE RunExtendedProc
@FileName varchar(255)
WITH EXECUTE AS OWNER
AS
EXEC xp_fileexist @FileName
RETURN
GOGRANT EXECUTE on RunExtendedProc to SomeLogin
EXECUTE AS LOGIN = 'SomeLogin'
EXEC dbo.RunExtendedProc 'C:\Temp\Test.xls'For that to work, at least based on my testing, that the original user must be able to impersonate the user the procedure is running under.
Those can get flakey with SQL Logins - I think other than just orphans so I just created and tested with a Windows login with no permissions other than mapped to database and execute the stored proc. No error executing as that account and just logged into SSMS as the Windows user and it was fine as well.
I've had that error before when I used a member of an AD group and the user isn't explicitly granted access with their individual login. I can't remember the others.Sue
Have to use SQL logins for this app.
There are situations where ownership chaining can become an issue. That's why I usually use certificates as that doesn't come into play.
July 17, 2019 at 3:55 pm
Good Morning. Only database administrator in a company, and a few days ago a developer raised the need to start using this SP (xp_fileexist). We had never used this SP before, and when we executed it with its account in the corresponding environment, we see that the SP resolves without problems, but it returns 0 (It does not exist) when my as SA returns 1 (If it exists). It creates the following one SP to mitigate security issues at the SQL level, but keep returning 0:
CREATE PROCEDURE sp_fileexist
@ FileName varchar (255)
WITH EXECUTE AS dbo
AS
EXEC xp_fileexist @FileName
RETURN
GO
The execute as, we change it only for an account that is SA of the instance, and the result we are still the same, but when we decided to do the test of adding the sysadmin role to the members of the developer account, it showed the value 1, which generates a total bewilderment. At Windows level, the user of the developer, the account of the engine, and the agent's account full permissions on the file you are trying to validate. Could somebody help me?
THANK YOU
REGARDS!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply