Updated: 5 December 2005

Microsoft SQL Server and Microsoft Windows can be configured to enable a SQL instance to connect to another SQL instance by forwarding the credentials of an authenticated Windows user. This arrangement is known as impersonation or delegation. Understanding how Microsoft SQL Server 2005 handles security for user impersonation is important when you use BULK INSERT or OPENROWSET. If a SQL Server user is logged on using Windows authentication, the user can read only those files that are accessible to the user account, independent of the security profile of the SQL Server process. If the SQL process has read access to a data file but a user that logged in by using Windows authentication lacks read access, the user cannot access this file through either Transact-SQL bulk import command—BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...).

Note:
If a user uses a SQL Server login, the security profile of the SQL Server process account is used.

For instance, consider a user that is logged in using Windows authentication. For the user to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the SQL Server process lacks permission to access the file; the user does not need to grant file-access permission to the SQL Server process.

The data file does not need to be on the same node as either the SQL Server process or the user. For example, if a user on Node_A has access to a data file on Node_B, and the delegation of credentials has been set appropriately, the user can connect to an instance of SQL Server running on Node_C, access the data file on Node_B, and bulk import data from that file into a table on Node_C.

Note:

The way in which SQL Server 2005 controls access to files addresses a security concern that was present in Microsoft SQL Server 2000 and earlier versions. Formerly, once a user was authenticated, access to external files was based on the security profile of the SQL Server process. When the SQL Server process had read access to a file, in the case of a user who lacked access to the file but was a member of the bulkadmin role, the user could import the file by using BULK INSERT and access the contents of the file.

 Bulk Importing from a Remote Data File

To use BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...) to bulk import data from another computer, the data file must be shared between the two computers. To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form, \\<Servername>\<Sharename>\<Path>\<Filename>. The user account used by SQL Server must have been granted the permissions required for reading the file on the remote disk.

For example, the following BULK INSERT command bulk imports data into the SalesOrderDetail table of the AdventureWorks database from a data file named newdata.txt. This data file resides in a shared folder named \dailyorders on a network share directory named salesforce on a system named computer2.

BULK INSERT AdventureWorks.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';
GO

Note:
This restriction does not apply to bcp because the file is read by the client independently of SQL Server.

 See Also

Other Resources

BULK INSERT (Transact-SQL)
Impersonation Overview
OPENROWSET (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

 Change History

Release	History
5 December 2005	New content: Added discussion of how to use a Transact-SQL command to import data from a remote data file.