Stored Proc and remote file

[andyc209]

I have a stored procedure running on our data server that uses database mail to send out emails with attachments. The problem i have is that the file to be attached is on our webserver.
if i map drives between boxes and use this new path or if i use the IP of the webserver the proc always returns the error attachment file is invalid but if i manually copy the file to the data server and then quote a local path it is fine.

I know all paths I use are correct - is there an issue with Procs accessing remote files? An example of my code is

DECLARE @FILENAME AS VARCHAR(255)
SET @FILENAME = N'z:\'+ @LOANREF +'-MA.PDF'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DominoMail',
@recipients = "me@me.com",
@subject = @SUBJECT,
@file_attachments = @FileName,
@body = @tableHTML,
@body_format = 'HTML';

 

[jrbarnett]

Remember that SQL Server has the permissions on the host (and network domain) granted to it by the service account.
This means:

1. Local files have to be accessible (at least read access)

2. Network addresses must be UNC, since the windows explorer shell network mapping (to your drive z: for example) won't be available to SQL server.

John

 

[andyc209]

thanks for the reply - if i change the path to the UNC I get access denied. I have, for now, set security to allow 'everyone' full access on the folder I am trying to copy from but still get the same error. e.g.

FolderA on server1 contains the file ABC.txt (security for everyone is set to allow)

I want to copy ABC.txt from FolderA on Server1 to FolderB on server2

DECLARE @MOVEFILE AS NVARCHAR(255)
SET @MOVEFILE = 'COPY /Y \\server1\folder1\ABC.txt /B \\server2\folderB\ABC.txt'
exec master..xp_cmdshell @movefile

gives me Access is Denied in SQL

 

[gmmastros]

Remember that SQL Server has the permissions on the host (and network domain) granted to it by the service account.

Here's how you check that. On the computer that has the database...

Click Start -> Run
Type Services.msc
Click OK

Scroll down to SQL Server
Right Click -> Properties
Click "Log On" tab

Take a look at the "Log On As" information. If you are using "Local System account", then you will not have permissions to any folder on any other computers. Forget about, can't be done.

What you can do instead... Create an active directory account just for SQL Server. Make sure this account has access to the computer running SQL Server AND access to the folders you need to copy the file from and access to the folder you need to copy the file to.

When creating the account in active directory, make sure you give this account a strong password and make sure you configure the password to never expire. Give this account the least amount of permissions to other computers as possible (for security reasons). You don't want a database hacker to have full access to your network.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andyc209]

Thanks George

i am running as Local so that is what it must be.
Might try a different route then. thanks

 

[gmmastros]

As I mentioned, you could create a windows active directory account, and then change the Log On for the SQL Server serice to use this new account.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom