There are special security considerations with OLE DB Provider for Index Services on the Windows NT operating system. SQL Server supplies a username and password on the current SQL Server login and on the login mapping set up in SQL Server of the form (current login, linked server) -> (remote login, remote password). However, OLE DB Provider for Index Services ignores the username and password and instead uses the Windows NT security context of the client (as if the client asked for a Windows NT Authentication mode connection). This means that OLE DB Provider for Index Services uses the Windows NT account under which SQL Server is running. Because this account is likely to be powerful, it can expose information about files to which the original SQL Server login has no privileges.
This concern has been addressed by giving SQL Server administrators full control over who has access to OLE DB Provider for Index Services through SQL Server. The administrator can control the login mappings so that no one other than those who have explicit login mappings can gain access to the server (for example, an Index Services linked server). The administrator can also disable ad hoc access against a given provider so that no one can access Index Services through the ad hoc route without using a linked server.
For example, if SQL Server is running under the Windows NT account sqlaccount, consider a linked server called mytextfiles that has been configured to point to a particular Index Services text search catalog. On Windows NT 4.0, when a SQL Server user executes a distributed query against mytextfiles, this query is executed under the privileges of the Windows NT account under which SQL Server is running (sqlaccount). Given this, the SQL Server security administrator must decide which SQL Server logins should have access to mytextfiles. This can be done by performing the following steps.
When a linked server is created with the sp_addlinkedserver stored procedure, by default all SQL Server logins have access to that server through self-mapping. The default mapping needs to be removed so that no one other than the approved logins can gain access to mytextfiles. This is accomplished by executing the following procedure:
-- Remove login mappings for all logins (by specifying NULL value for the @locallogin
parameter)
exec sp_droplinkedsrvlogin 'MyTextFiles', NULL
For each SQL Server login (either SQL Server Authentication or Windows NT Authentication) to be given access to the mytextfiles linked server, execute the following stored procedure:
-- Add a self mapping for local login to itself
exec sp_addlinkedsrvlogin 'MyTextFiles, true, 'local_login'
Remove the ability for ad hoc access to Index Services text search catalogs using the OPENROWSET() function. This task is accomplished through SQL Server Enterprise Manager by using the Linked Server Properties dialog box, which can be brought up by right-clicking on the corresponding linked server in SQL Server Enterprise Manager, and then clicking Properties.