User impersonation allows the data file to reside on a different computer than either the SQL Server process or the user. Understanding how SQL Server version handle security for user impersonation is important when you use BULK INSERT or OPENROWSET.
This arrangement is known as impersonation or delegation.
LOAD BULK DATA INTO SEQUEL PRO WINDOWS
SQL Server and Microsoft Windows can be configured to enable an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user.
The user does not have to grant file-access permission to the SQL Server process. With access to the data file, the user can import data from the file into a table even if the SQL Server process does not have permission to access the file. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the user account requires read access to the data file. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.įor example, consider a user who logged in to an instance of SQL Server by using Windows Authentication. To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). A login using SQL Server authentication cannot be authenticated outside of the Database Engine. If a user uses a SQL Server login, the security profile of the SQL Server process account is used.
In addition to the regular table hints, such as TABLOCK, the BULK clause can accept the following specialized table hints: IGNORE_CONSTRAINTS (ignores only the CHECK constraints), IGNORE_TRIGGERS, KEEPDEFAULTS, and KEEPIDENTITY. When used in an INSERT statement, OPENROWSET(BULK.) supports table hints. The basic syntax for bulk importing data is: To bulk import data, call OPENROWSET(BULK.) from a SELECT.FROM clause within an INSERT statement. The OPENROWSET(BULK.) function allows you to access remote data by connecting to a remote data source, such as a data file, through an OLE DB provider. The OPENROWSET bulk rowset provider is accessed by calling the OPENROWSET function and specifying the BULK option. Use a Format File to Map Table Columns to Data-File Fields (SQL Server).Use a Format File to Skip a Table Column (SQL Server).Use Unicode Native Format to Import or Export Data (SQL Server).Use Unicode Character Format to Import or Export Data (SQL Server).Use Native Format to Import or Export Data (SQL Server).Use Character Format to Import or Export Data (SQL Server).Use a Format File to Bulk Import Data (SQL Server).Specify Field and Row Terminators (SQL Server).Keep Nulls or Use Default Values During Bulk Import (SQL Server).Keep Identity Values When Bulk Importing Data (SQL Server).Examples of Bulk Import and Export of XML Documents (SQL Server).For a description of the BULK INSERT syntax, see BULK INSERT (Transact-SQL). This functionality is similar to that provided by the in option of the bcp command however, the data file is read by the SQL Server process. BULK INSERT statementīULK INSERT loads data from a data file into a table. For more information, see "Security Considerations," later in this topic. When you use BULK INSERT or OPENROWSET(BULK.), it is important to understand how SQL Server version handles impersonation.
0 kommentar(er)
