xp_cmdshell 'dir "\\IPAddress\d$\" /b'; gives me access denied .

[kinjalsoni]

I want to access file from remote PC (LAN PC)but this line EXEC master..xp_cmdshell 'dir "\\192.168.0.2\d$\" /b';
gives "Access denied" message.

[ck9663]

That means the account that the xp_cmdshell is using does not have the necessary rights on that folder. Not because you're the one logged in does not always mean your account will be used by the xp_cmdshell command.

More in here

Good Luck!!!

~~ CK

[Jerry Winston]

because the account the sql server is using on the remote system does not have network privileges on the share. The best way to fix this is to create a windows login with permissions on this share, then use that windows login as a credential to assign to a SQL Login. When connected with the SQL Login the server will use the windows credential mapped to it when the server attempts to access the share. If you take a look at windows event viewer on the file share machine, you will see a list of failed attempts your server has made to the share. Once the Login running the xp_cmdshell command has a windows credential assigned to it you will see the windows login appear in the 'user' column of the event log.

FYI, whenever something goes wrong connecting to a file server, web server, or any server, the best place to start is with the log. You can often times find out who, what, how and what action failed.

[Jerry Winston]

Create the credential

CREATE CREDENTIAL [xpCmdshellCredentialGuy]
WITH IDENTITY = N'myWinDomain\myWinUser', SECRET = N'pwdpwd'
 

Create the login:

CREATE LOGIN [cmd_User] 
WITH PASSWORD=N'TopSeeCret', 
DEFAULT_DATABASE=[MyDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, CREDENTIAL = [xpCmdshellCredentialGuy]
 

Create the user:

USE [MyDB]
CREATE USER [cmd_User] FOR LOGIN [cmd_User] WITH DEFAULT_SCHEMA=[mySchema]
 

[kinjalsoni]

@b0010100
My PC is not in domain so is it require?

[NeoPa]

It should still be possible. I think b0010100 has a much better understanding of the SQL Server part of this puzzle than I, but remote connections to any Windows NT based PC are possible without a Windows domain being set up.

Why don't you describe the topology of your setup, including where your PC, the destination PC and the SQL Server PC fit in to it, and we can see what we can come up with.

Another approach, if you have the access, is to set up a specific share on the destination PC which has permissions set specifically for the account used by your SQL Server's SQL Server Agent account. D$ is what is known as an Admin share. Only accounts with administrator privileges can access them, even were other accounts to be assigned permissions. A share you set up yourself is far more flexible.

Welcome to Bytes!

[kinjalsoni]

@NeoPa
Hi...

Can you please write setups how to share folder with sql server agent account rights.because i tried a lots of time but still giving me a access denied.

[kinjalsoni]

@kinjalsoni
sorry tried*

[Jerry Winston]

NeoPa is right about domains. It doesn't have to be setup.
When you share a windows folder it's never just plain "shared". It's always shared with a user, group, ect. Find out who your folder is shared with in the properties->sharing&security->permissions of the folder.

Make sure your server,through credentials, is connecting as someone that appears in the permissions for the share.

If the server is not connecting as a login or member of a group that the share trusts. you can create an user on the share machine for the SQL server to use as its credential. Or you can add the server to a group on the share machine that has rights to the folder.

[NeoPa]

NeoPa: Why don't you describe the topology of your setup, including where your PC, the destination PC and the SQL Server PC fit in to it, and we can see what we can come up with.

kinjalsoni: Can you please write setups how to share folder with sql server agent account rights.because i tried a lots of time but still giving me a access denied.

Without even a basic understanding of how your network is set up this would be a waste of time. If I get a response from you to my earlier request, I may be in a postion to help further.