Solved

What user is used by SQL server when connecting to remote file

Posted on 2014-02-12
7
384 Views
Last Modified: 2014-02-12
I'm running this SQL code
CREATE ASSEMBLY dlls
/*FROM '\\192.168.50.17\SQL-dll\SQL-dll.dll'*/
FROM 'c:\temp\SQL-dll.dll'
GO

Open in new window

This code is run on 192.168.50.17, in SSMS remotely connected to the SQL server. It expects the dll file to be at c:\temp at the SQL server (WinWebServer2008), and it works.
However, I would like to connect to the file that is located on 192.168.50.17 (developer machine), but this does not work. Probably because there's not sufficient user rights from SQL Server to access the file system on 192.168.50.17.
Which user is SQL server using here? It is not the same user as I am connecting the SQL session with, because that user has access to the shared folder on 192.168.50.17.
Alternatively, how can I figure out which user is trying to connect to the shared folder on 192.168.50.17 (Win7)
0
Comment
Question by:lefodnes
  • 4
  • 3
7 Comments
 
LVL 19

Expert Comment

by:strivoli
ID: 39853051
The account usually involved is the (local)SYSTEM or the account invoked to start SQL's service.
In order to be sure which account is used and is access denied to the computer and/or share, set the auditing on the share/folder and have a look at the Windows Security Logs.
0
 

Author Comment

by:lefodnes
ID: 39853212
Thanks strivoli. You helped me a little on the way.

I configured auditing for all users, and I watched it while running the same query from either dev machine or the server.

When running from the server SSMS, it connects from the SQL server as my user name (I'm logged in on it), and then it works (using the \\192.168.50.17\SQL-dll\SQL-dll.dll file path).

When running from the dev machine, it connects from the SQL server as "NT-AUTHORITY\Anonymous". Even if I grant Anonymous rights to the folder on the DEV machine, it cannot read the file. Why is it different? Isn't the same SQL processed, no matter where SSMS is connected from?

Is the anonymous on my dev machine not the same as anonymous on the SQL server machine?
0
 
LVL 19

Expert Comment

by:strivoli
ID: 39853285
I'll send you an official MS link that shows you how to allow anonymous access to a folder. Shortly:
a. Need to allow anonymous at the File System level,
b. Need to allow anonymous at the share level,
c. Need to allow the share to be accessed anonymously.
Will be more precise when I'll send you the full link (don't have it handy and can't find it on the net right now).

Anyway, I don't remember I had to set anonymous access when I faced same problem.

Are the 2 servers members of the same domain?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 19

Expert Comment

by:strivoli
ID: 39853301
I didn't respond to your questions: anonymous is the same independently from the computer it tries to access from. Your anonymous rights aren't enough. This is why I'll send you full details.
0
 

Author Comment

by:lefodnes
ID: 39853447
thanks a lot for your answers. the two different machines are not on the same domain, nor on the same lan.
looking forward to see your link about this issue.thanks again.
0
 
LVL 19

Accepted Solution

by:
strivoli earned 500 total points
ID: 39853606
Creating an anonymous share in Windows Server 2008R2 is the link I was talking about. I remembered it was an official MS link but it isn't. I've used the instructions on the link to create an anonymous access to a folder on one of my servers.
Consider carefully the security implications of creating anonymous shares.
0
 

Author Closing Comment

by:lefodnes
ID: 39853708
Super! That trick did it :)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This Micro Tutorial will give you a introduction in two parts how to utilize Windows Live Movie Maker to its maximum editing capability. This will be demonstrated using Windows Live Movie Maker on Windows 7 operating system.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now