How to allow a SQL login access to a folder on another server.

Question

How to allow a SQL login access to a folder on another server.

Duran

SSChampion

Points: 13259
More actions
March 30, 2011 at 9:13 am

#237054

Hello everyone,
I hope somebody can help. I need to allow a SQL login to access a folder on another server (not another database). I think I have to allow the account to impersonate another account (?), but I am not sure how to go about this, or if its even the best way to actually do that, perhaps there is a better way, perhaps 'execute as'? We are running mixed mode authentication.
Thank you for reading.
Regards,
D.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Typically when SQL Server accesses resources on the network it uses the SQL Server service account so you'd need that account to have permissions to the network resources. I don't think that, within T-SQL, you can make SQL Server pass the connected user's credentials.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lowell SSC Guru Points: 323546 More actions · Answer 2

there is a way, but it exposes a users passwords in code; that makes it extra creepy with a domain password

it's possible to map a network drive with a domain users username and password, do your work, and then remove the mapped drive;

in this example, I'm creating a "X:\" mapped drive:

--exec master.dbo.xp_cmshell 'NET USE G: \\UNCPath\d$ /user:domain\user password'

CREATE TABLE #Results(

ID int identity(1,1) NOT NULL,

TheOutput varchar(1000))

insert into #Results (TheOutput)

exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.

insert into #Results (TheOutput)

exec master..xp_cmdshell 'NET USE X: \\DEV223\c$\Data /user:mydomain\lowell NotARealPassword'

insert into #Results (TheOutput)

exec master..xp_cmdshell 'dir X:'

insert into #Results (TheOutput)

EXEC master..xp_cmdshell 'NET USE X: /DELETE'

select * from #Results

DROP TABLE #Results

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!

Duran SSChampion Points: 13259 More actions · Answer 3

Thanks for taking the time to answer, I figured that it would use the service account, I shall have to find out why this is not happening.

Lowell, thanks for the snippet, I take it I'd only need the commented out part or all of it? (I'n not exactly great at t-sql).

Regards,

D.