Creating Stream DATABASE in a remote server

Question

Whith the assistance of a very good fellow from this forum (Mr. DJHnz) i solve my first issue regarding the creation of a stream database Now i'm facing another issue I'm giving you the code:

---

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_AddStreamDB]    Script Date: 12/21/2009 09:55:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_AddStreamDB](
    -- Add the parameters for the stored procedure here
    @DPath varchar(MAX),
    @DBName varchar(50),
    @Qchar varchar(1) = "'"
) AS
BEGIN_TRY:
    SET QUOTED_IDENTIFIER ON;
    SET NOCOUNT ON;
-- Insert statements for procedure here 
    DECLARE
    @ErrMsg nvarchar(4000),
    @DBName1 varchar(50),
    @DBName2 varchar(50),
    @DBNamefs varchar(50),
    @DBNamelog varchar(50),
    @FileGroupN varchar(100),
    @DATName varchar(MAX),
    @LOGName varchar(MAX),
    @FSName varchar(MAX),
    @CreateSdb nvarchar(MAX),
    @Statement nvarchar(MAX)
    SET @DBName1 = (@DBName + '1')
    SET @DBName2 = (@DBName + '2')
    SET @DBNamefs = (@DBName + 'fs')
    SET @DBNamelog = (@DBName + 'log')
    SET @FileGroupN = (@DBname + 'StreamGroup')
    SET @DATName = (@Qchar + @DPath + @DBName +'_dat.mdf' + @Qchar)
    SET @LOGName = (@Qchar + @DPath + @DBName +'_log.ldf' + @Qchar)
    SET @FSName =  (@Qchar + @DPath + @DBName + '_fs' + @Qchar)
SET @CreateSdb =('CREATE DATABASE ' + @DBName + ' ON PRIMARY (NAME = ' + @DBName1 + ', FILENAME = ' + @DATName + '), FILEGROUP ' + @FileGroupN + ' CONTAINS FILESTREAM (NAME = ' + @DBNamefs + ', FILENAME = ' + @FSName + ') LOG ON (NAME = ' + @DBNamelog + ', FILENAME = ' + @LOGName + ')')
    SET @Statement = '   '
BEGIN_CATCH:
SELECT ERROR_MESSAGE() as ErrorMessage;
SELECT @ErrMsg = ERROR_MESSAGE()

EXEC master.sys.sp_executesql @CreateSdb, @Statement

RAISERROR (@ErrMsg,1,1)
RETURN 0 
END_CATCH:
END_TRY:

---

So far to point everything works fine until the remote server tries to create the neccessary files for the stream DB

then he gives the following error:

Unable to open the physical file "C:\sqlDATA\RemoteDB_fs". Operating system error -2147024891: "0x80070005(Access is denied.)".

---

The name of the drive C:\ lies on the remote machine (a machine near to me in the same network with Windows server 2003; later i will run the program for my ISP machine) the subfolder sqlDATA\ is already there i have created manually as it should be. In my local machine the all package works fine and the DATA BASE created fine but the issue starts when i use remote server.

NOW I NEED THE HELP:

Why i receive this ERROR?

Answer 1

The SQL Server service account does not have rights on C:\SQLData

Answer 2

As @gbn explained and also:

• C:\sqlDATA directory must exist.
• The \RemoteDB_fs sub-directory for file-stream must not exist.