Identifying a Data Source by Using an Ad Hoc Name
When the OLE DB data source will not be referenced frequently enough to warrant configuring a linked server, an ad hoc name can be used as a table reference. In SQL Server 2005, you can use the OPENROWSET and OPENDATASOURCE functions to provide an ad hoc name.
Controlling who can access a specific data source is only available through linked servers. Because this control is not available for ad hoc names, ad hoc names are disabled by default for all OLE DB providers except the SQL Native Client OLE DB Provider.
Important
For each instance of SQL Server, members of the sysadmin fixed server role can enable or disable the use of ad hoc connector names for an OLE DB provider by using the DisallowAdhocAccess provider option and the Ad Hoc Distributed Queries server configuration option. When ad hoc access is enabled, any user logged on to that instance can execute SQL statements that contain ad hoc connector names referencing any data source on the network that can be accessed by using that OLE DB provider. To control access to data sources, members of the sysadmin role can disable ad hoc access for that OLE DB provider, thereby limiting users to only those data sources referenced by linked server names defined by the administrators. By default, ad hoc access is enabled for the SQL Native Client OLE DB Provider and disabled for all other OLE DB providers.
Both OPENROWSET and OPENDATASOURCE provide ad hoc connection information. You can use these functions to specify all the information required to access the OLE DB data source; however, you cannot use OPENROWSET and OPENDATASOURCE interchangeably:
- You can use OPENROWSET wherever the OLE DB provider returns rowsets either by specifying a table (or view) name or by specifying a query that returns a rowset. OPENROWSET can be used instead of a table or view name in a Transact-SQL statement.
- Use OPENDATASOURCE only when the provider exposes rowsets and uses the catalog**.schema.object notation. OPENDATASOURCE can be used in the same locations in Transact-SQL syntax where a linked server name can be used. Therefore, in the catalog.schema.**object notation, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or a view.
OPENROWSET and OPENDATASOURCE should be used only for accessing external data in ad hoc situations when a permanent linked server cannot be configured. OPENROWSET and OPENDATASOURCE do not provide all of the functionality available from a linked server, such as management of login mappings, the ability to query the metadata of the linked server, and the ability to configure various connection settings such as time-out values.
.gif "ms187873.security(en-US,SQL.90).gif") Security Note: |
|---|
| When connecting to another data source, SQL Server impersonates the login appropriately for Windows authenticated logins; however, SQL Server cannot impersonate SQL Server authenticated logins. Therefore, for SQL Server authenticated logins, SQL Server can access another data source, such as files, nonrelational data sources like Active Directory, by using the security context of the Windows account under which the SQL Server service is running. Doing this can potentially give such logins access to another data source for which they do not have permissions, but the account under which the SQL Server service is running does have permissions. This possibility should be considered when you are using SQL Server authenticated logins. |
The arguments of OPENROWSET and OPENDATASOURCE do not support variables. The arguments must be specified as string-literals. If variables must be passed in as arguments, a query string that contains the variables can be constructed dynamically and executed by using the EXECUTE statement.
See Also
Concepts
Accessing External Data
Distributed Queries
Other Resources
OPENDATASOURCE (Transact-SQL)
OPENROWSET (Transact-SQL)