PRB: Profiler Errors If Connected Using SQL Authentication via TCP/IP Socket (235366)

SYMPTOMS

SQL Server Profiler can connect to Microsoft SQL Server using either Windows NT authentication or SQL Server authentication. If it connects using SQL Server authentication with the TCP/IP socket network library, the following error message occurs:

This operation could not be completed because your security context could not be impersonated.

This does not occur under SQL Server 6.5. It represents a change in behavior from SQL version 6.5 to SQL version 7.0.

CAUSE

For security reasons, SQL Server Profiler and the related system stored procedures such as xp_trace_addnewqueue require that the Profiler clients be successfully impersonated. When a client connects to SQL Server using the TCP/IP socket network library and SQL Server authentication, it can not be impersonated because the security context of the client is unavailable. This security context is available if the client is connected using Windows NT Authentication or through other Inter-Process Communications (IPCs) that support impersonation such as Named Pipes or Multi-Protocol.

WORKAROUND

Use Windows NT authentication for TCP/IP socket network library connections, or use Named Pipes or Multi-Protocol network libraries if SQL Server authentication is desired. To configure SQL Server to listen on Multi-Protocol, use SQL Server Network Utility and Add Multi-Protocol Net-Library. On the client side where SQL Server Profiler runs, use the Client Network Utility to change the default Net-Library to Multi-Protocol. For more information, see SQL Server Books OnLine for details.