Creating shared folder between two servers

Question

I need to create an SQL log shipping between 2 databases,

The source is on an AWS EC2 instance running Windows Server 2008 R2 and SQL Server 2008

The destination is on an Azure VM running Windows datacenter 2012 and SQL Server 2012

This process requires the following:

• A. source server write a file

  B. destination server copy the file to a local folder on it

  C. the destination server uses the file

all these tasks are done by the sql server process

Now i have problem of creating some shared folder between the instances, i tried to do the following :

step A is done to a local folder on the machine,

On step b the sql server tries to access that folder using this format (and that copy the files from it to a local folder):

\\serveraddress\drive$\folder

after granting access to everyone to that folder in the source server and enabling sharing and enabling the guest user

Yet i keep having access problems and the copy process fails.

What should one do ?

Thanks

EDIT :

I ended up to no success trying any of the solutions that were suggested, some how the sql server could not gain access to folders that the user did had access to,

So instead of digging in to that i have chosen to use 3rd party software to sync information between the 2 servers such as dropbox/svn ect.. and by using the same location for the shared folder on both servers i could in a very simple way use the network location:

\\localhost\...

and both computers would act as if it is on the network but the folder was synced and shared on the local instance so there was no permission problem on it.

Not the ideal solution but it works.

Solution 1

Step one. Don't use the <drive>$ shares - these are administrative shares and need administrator privileges.

Then on the target server:

Create your own share, e.g.:

net share LOG_SHIPPING=d:\data\log_shipping /grant:everyone,full /cache:none /remark:"something meaningful"

Create a local account on the target server:

net user log_shipping_user <PASSWORD> /comment:"something meaningful" /passwordchq:no /add

Set permissions approrpriately, e.g.:

icacls d:\data\log_shipping /grant "log_shipping_user:(OI)(CI)(M)"

Then on the source server, make the in-bound connection using the above user:

net use \\<TARGET SERVER>\log_shipping /user:<TARGET_SERVER>\log_shipping_user /password:<PASSWORD>

That should work. Do the same in the opposite direction for connections the other way.

Solution 2

Are the users and servers all in the same Active Directory domain, or in domains trusted by the source server? If not, the users are attempting to log on anonymously over the network. In Windows versions starting with 2003/XP, the Everyone group does not give permissions for that. You need to grant permissions to the ANONYMOUS LOGON and NETWORK groups.

Comments

[Matan L]

they are not, and if that would help i will be happy if you point me to how to do so, i added these permissions and still cannot access...

[Matan L]

something is odd, i can connect to that folder from my own computer without using the user and password now, but for some reason the azure server still wants username and password when trying to connect, i also tried to connect from a different vm on the same network in azure and it worked...

[Matan L]

I did as you mentioned, and if i try to access that location in the windows explorer it works fine but it seems as if the SQL server doesnt have access... the log says access denied to that path ... i run into this social.msdn.microsoft.com/Forums/sqlserver/en-US/… but i couldnt add a different computer to the share options ...

[Simon Catlin]

Aha. I see that you've found an alternate solution. However, for completeness, from reading the forum post above, it would appear that the underlying issue is that the SQL Agent runs (by default) as the Local System account. I've never tried this next bit on non-domain servers, but in a domain model, you could add the server #1's computer account (<SERVERNAME>$) to the local group that controls access to the log shipping data on server #2. Like I say, I can't remember if this is possible in a non-domain setup.