Permissions required to use Bulk Load with SQL Server 2005

[Andrew Chilcott]

I have successfully created a new table in SQL Server 2005 that holds just
two fields, an identity field and an XML field. Using the following code, I
have also successfully loaded an XML file from the local file system into
the table:

cmd2.CommandText = "INSERT INTO RMXemail SELECT rmxemail FROM (SELECT * FROM
OPENROWSET (BULK 'c:/INetPub/wwwroot/LCNETSQL/Emails/sample.xml',
SINGLE_BLOB) AS rmxemail) AS R(rmxemail);"

So far so good. Now to deploy it.

The server on which the website is loaded does not have SQL Server 2005
installed - it is on a separate server. My ISP has provided a connection
string to access the database from the website and this works perfectly with
me being able to access the data using ADO. However, when the above code is
run I get the following error message:

Microsoft OLE DB Provider for SQL Server error '80040e14'
You do not have permission to use the bulk load statement.

/rmxemail.asp, line 35

and the following comment from my ISP

"I think the problem is that the SQL Server user needs permissions on the
file you are trying to read from. Unfortunately, because the SQL Server user
is local to the server and the servers do not share a common domain, I can't
grant these permissions.

I think we will need to move your site to the same server as the SQL
database, unless you can find some documentation which states how we can set
permissions for the SQL 2005 user on one server to access files on another
server."

Is there a way of granting the SQL Server User access rights to read from
the file system of another server?

[Dan Guzman]

Post by Andrew Chilcott
Is there a way of granting the SQL Server User access rights to read from
the file system of another server?

Yes but this is problematic when the servers aren't in a domain.

Rather than use OPENROWSET...BULK for this task, you might find it easier to
read the file contents and insert from your application code. If the file
is reasonably sized, you can do this in a single insert:

cmd2.CommandText = new SqlCommand(
"INSERT INTO RMXemail VALUES(@FileContents);", connection);
SqlParameter parameter = command.Parameters.Add("@FileContents",
SqlDbType.Xml);
System.IO.StreamReader sr = new
StreamReader(@"c:/INetPub/wwwroot/LCNETSQL/Emails/sample.xml");
parameter.Value = sr.ReadToEnd();
sr.Close();
cmd2.ExecuteNonQuery();
--
Hope this helps.

Dan Guzman
SQL Server MVP

Post by Andrew Chilcott
I have successfully created a new table in SQL Server 2005 that holds just
two fields, an identity field and an XML field. Using the following code, I
have also successfully loaded an XML file from the local file system into
cmd2.CommandText = "INSERT INTO RMXemail SELECT rmxemail FROM (SELECT * FROM
OPENROWSET (BULK 'c:/INetPub/wwwroot/LCNETSQL/Emails/sample.xml',
SINGLE_BLOB) AS rmxemail) AS R(rmxemail);"
So far so good. Now to deploy it.
The server on which the website is loaded does not have SQL Server 2005
installed - it is on a separate server. My ISP has provided a connection
string to access the database from the website and this works perfectly with
me being able to access the data using ADO. However, when the above code is
Microsoft OLE DB Provider for SQL Server error '80040e14'
You do not have permission to use the bulk load statement.
/rmxemail.asp, line 35
and the following comment from my ISP
"I think the problem is that the SQL Server user needs permissions on the
file you are trying to read from. Unfortunately, because the SQL Server user
is local to the server and the servers do not share a common domain, I can't
grant these permissions.
I think we will need to move your site to the same server as the SQL
database, unless you can find some documentation which states how we can set
permissions for the SQL 2005 user on one server to access files on another
server."
Is there a way of granting the SQL Server User access rights to read from
the file system of another server?