BY DEFAULT THE VARIOUS OLE DB PROVIDERS ON THE SERVER ALLOW AD HOC...

3. By default the various OLE DB providers on the server allow

ad hoc access. View the list of the providers on your server by

connecting to your SQL Server instance in the SSMS Object

Explorer. Then expand Server Objects | Linked Servers | Providers.

Right click on a provider and choose Properties to view its

properties. Is it set to disallow ad hoc access? What about the

other providers?

Using the OPENROWSET Function

The OPENROWSET function can be used to access data from data sources

external to SQL Server. This can be a great way to interact with data in external

sources without requiring administrators to first configure a linked server. The

OPENROWSET function can also be used as yet another way to bulk load data

into SQL Server.

OPENROWSET is a table-valued function. That means that the result of the

call to the function is actually a table. The implication is that you use the

OPENROWSET( ) function call in place of a table reference in statements that

work with tables (like SELECT, INSERT, UPDATE, and DELETE).

Start by seeing how to use SQL Server to access external data.

The basic syntax of the OPENROWSET Function looks like this:

OPENROWSET

(

'provider_name',

{ 'datasource'; 'user_id'; 'password' | 'provider_string' },

{ [ catalog. ] [ schema. ] object | 'query'

)

The “provider_name” is the name of the OLE DB provider you wish to use

when connecting to the external database. For SQL Server, you can use SQLNCLI.

You can find out what the names of your other available providers are by looking

at the documentation for the providers, looking in the registry (HKLM\SOFTWARE\

Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers), and,

of course, searching the Internet for resources.

The provider will need to know where and as whom to connect. There are two

ways to do this. You can use a canonical format of ‘datasource’; ‘user_id’; ‘password’”

or we can specify a “provider_string.”

The “‘datasource’; ‘user_id’; ‘password’” is a single parameter that has semi-colon

delimited elements. This method of providing connection details is the easiest to

use, but requires that you use SQL Authentication (assuming the target is another

SQL instance). This means the credentials are entered as clear text in the syntax.

That’s alarming.

The “provider_string” method is a little harder to get right, but makes it possible

to use a trusted connection (Windows Authentication) to the external data source.

This can be tricky to get right, but once you get it configured, it is a much more

secure way to connect. You can also use additional information in your connection

strings to further control the behavior of the connection.

Once you have specified how to connect, you finally need to specify what data

to retrieve. You again have a choice. You can either state a database.schema.object

qualified object identifier, or you can write a query to be executed by the external

data source.

If you use the database.schema.object identifier to name an object to return data

from, the referenced object needs to be a valid table or view, and the identity the

connection is made with must have permissions to the object. In the documentation

of the statement you see the word “catalog” used instead of “database.” Again,

catalog is just what the ANSI specification calls a database.

xam

arning

Remember that ad hoc queries are disabled by default. The administrator

has to have enabled them using sp_configure.

Querying Data with OPENROWSET

Let’s see how to use the OPENROWSET function to query some data from a remote

instance of SQL Server. Assume there is another instance of SQL Server named

WebSQL. You want to query (SELECT) data from the WebSQL.AdventureWorks2008.

Sales.SalesOrderHeader table. The following statements show a number of variations,

all of which would work. Pay attention to how the OPENROWSET() function call is

placed where you would normally find a table name in a SELECT statement:

--Using the 'datasource';'loginname';'password' format

--and referencing an object

SELECT * FROM OPENROWSET(

'SQLNCLI',

'WEBSQL';'sa';'P@ssw0rd',

AdventureWorks2008.Sales.SalesOrderHeader);

--and supplying a query

'SELECT * FROM AdventureWorks2008.Sales.SalesOrderHeader');

--Using the 'provider string' format

--and passing a query

'Server=WEBSQL;Trusted_Connection=Yes;Database=AdventureWorks;',

In the examples where the canonical format is used you see that you can

read the credentials used to connect in clear text. That is a major security issue.

Of course, in the samples above the “sa” account is being used to connect as well,

and that is another big no-no. It is shown that way in this example to highlight

how much of a security risk these types of queries can be if they are not written

and reviewed carefully.

The “provider_string” version of the preceding query was able to use a “Trusted

Connection,” but that string can be difficult to configure. It requires that the servers

interact properly with Active Directory and takes some configuration by the

Windows administrator to make it work. However, once it is properly configured it

is a much more secure and controllable way to connect.