Security

When a consumer connects to an OLE DB provider, the provider typically requires a user ID and a password, unless the consumer wants to be authenticated as an integrated security user. In the case of distributed queries, SQL Server acts as the OLE DB provider’s consumer on behalf of the SQL Server login that executes the distributed query. SQL Server maps the current SQL Server login to a user ID and password on the linked server.

These mappings can be specified by the user for a given linked server and can be set up and managed by the system stored procedures sp_addlinkedsrvlogin and sp_droplinkedsrvlogin. By setting the initialization group properties DBPROP_AUTH_USERID and DBPROP_AUTH_PASSWORD through IDBProperties::SetProperties, the user ID and password determined by the mapping are passed to the provider during connection establishment.

When a client connects to SQL Server through Windows NT Authentication, SQL Server does not propagate the Windows NT Authentication to a provider. In this case, the Windows NT authenticated logins must map to a specific user ID and password to access a linked server.

After the security context used for the connection is determined, the authentication of this security context and the permission checking for that context against data objects in the data source are entirely up to the OLE DB provider.

For more information about sp_addlinkedsrvlogin and sp_droplinkedsrvlogin, see SQL Server Books Online.