July 12, 2018 at 8:28 am
I need to query an access .mdb file in a locked down network drive.
I was planning on using OPENROWSET to pull the information I need into a temp table to do what I need, send the report out and then close the connection. When this was all done in test locally it was fine, as soon as I tried it over the network it all started to hit the fan.
I have created a credential with permissions to the shared file.
I have altered a login to use that credential.
I have created a proc to run the code using the with execute as to the altered login.
I have tested it has permissions by running this
Declare @out int
EXEC master.dbo.xp_fileexist '\\server\folder\file.mdb', @out output
select @out
SELECT IIF(@out = 1, 'Exists', 'Not Exists');
However, when I run the openrowset command I get
Msg 15274, Level 16, State 1, Line 42
Access to the remote server is denied because the current security context is not trusted.
So I set the database to trustworthy on
but now get
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
When I check if the file exists again from the code it says not exists. When I check the auditing in windows security on the server it tells me that its trying to access the file form the NT account even though it has the execute as which specifies a windows domain account. So when trustworthy is on the execute as doesn't appear to work anymore.
Has anyone got a freaking clue how the hell you get around this? I'm open to using another method!
Cheers
July 12, 2018 at 11:23 am
If I understand correctly, and I may not, you can't just impersonate a Windows account using a SQL login. When I think about the security hole that would represent, it's huge. However, what is usually done is that tasks of this nature are executed by the SQL Agent, and thus run under the security context of the SQL Server Agent Service Account, which, when best practices are being followed, is usually a domain account. You usually then set up that domain account with all the global groups or domain local groups it needs to be able to access things like network shares or any other object under the auspices of the Windows security model. It doesn't quite sound like this is what is taking place in your situation. If you can elaborate further or correct my understanding, please do so.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 13, 2018 at 12:57 am
The Sql logins are windows Auth but yes, when the stored proc runs it sees to run under the credentials of the Sql server agent which uses the NT account.
The file that I need to access is highly restricted. I wouldn't want a domain account to be able to access it. Allowing the domain account to access secure shared drives would open up access to anyone who can create a stored procedure on the server then...
When the database trustworthy setting is set to off then procedure does correctly run under the windows credential and "reach" the file. However because the database isn't marked as trustworthy then the openrowset command won't work.
If the database is set to trustworthy then the execute as is bypassed and it is run under the sql server agent credentials. So I am damned if if do and damned if I don't!
Seems ridiculous that SQL doesn't have some option to do this?
Perhaps I need to do it via SSIS but it just seems overkill for the simple report I need to spit out!
July 13, 2018 at 12:03 pm
rob.lewis 86087 - Friday, July 13, 2018 12:57 AMThe Sql logins are windows Auth but yes, when the stored proc runs it sees to run under the credentials of the Sql server agent which uses the NT account.The file that I need to access is highly restricted. I wouldn't want a domain account to be able to access it. Allowing the domain account to access secure shared drives would open up access to anyone who can create a stored procedure on the server then...
When the database trustworthy setting is set to off then procedure does correctly run under the windows credential and "reach" the file. However because the database isn't marked as trustworthy then the openrowset command won't work.
If the database is set to trustworthy then the execute as is bypassed and it is run under the sql server agent credentials. So I am damned if if do and damned if I don't!
Seems ridiculous that SQL doesn't have some option to do this?
Perhaps I need to do it via SSIS but it just seems overkill for the simple report I need to spit out!
I'm at a loss to understand the security concern. If the file is locked down to read-only access to a specific global group within AD, and the service account is a member of that AD group, why would that be a problem? If anyone that can create a stored procedure can make use of the service account's permissions, you have a much larger security hole to worry about than this file share.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 16, 2018 at 1:14 am
sgmunson - Friday, July 13, 2018 12:03 PMrob.lewis 86087 - Friday, July 13, 2018 12:57 AMThe Sql logins are windows Auth but yes, when the stored proc runs it sees to run under the credentials of the Sql server agent which uses the NT account.The file that I need to access is highly restricted. I wouldn't want a domain account to be able to access it. Allowing the domain account to access secure shared drives would open up access to anyone who can create a stored procedure on the server then...
When the database trustworthy setting is set to off then procedure does correctly run under the windows credential and "reach" the file. However because the database isn't marked as trustworthy then the openrowset command won't work.
If the database is set to trustworthy then the execute as is bypassed and it is run under the sql server agent credentials. So I am damned if if do and damned if I don't!
Seems ridiculous that SQL doesn't have some option to do this?
Perhaps I need to do it via SSIS but it just seems overkill for the simple report I need to spit out!I'm at a loss to understand the security concern. If the file is locked down to read-only access to a specific global group within AD, and the service account is a member of that AD group, why would that be a problem? If anyone that can create a stored procedure can make use of the service account's permissions, you have a much larger security hole to worry about than this file share.
By default, if you try and access files then it will use the account of the SQL server agent login, correct?
So if we made it so the account could access specific folders and files we want locked down, then the handful of people who have create procedure permissions could in theory access these folders and files from the procedure.
I'm not sure what you are suggesting is a security issue here?
It seems the best way to handle this solution is in a package that creates a mapped drive to the folder does what it needs to do and un-mapps the drive. Still testing this though.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply