Ad hoc distributed queries включить

В SQL Server от компании Microsoft для управления ресурсами сервера используются параметры конфигурации, в данном материале мы поговорим о системной процедуре sp_configure, с помощью которой можно просматривать и изменять эти параметры.

Для начала давайте поговорим о том, для чего вообще нужны параметры конфигурации, и стоит ли их изменять.

Параметры конфигурации Microsoft SQL Server

Параметры конфигурации SQL сервера – это параметры, которые применяются ко всему экземпляру сервера. С помощью данных параметров можно оптимизировать ресурсы с целью увеличения производительности или расширить базовый функционал (включить дополнительную возможность).

Если параметры поддерживаются на нескольких уровнях, то параметры конфигурации на уровне экземпляра имеют меньший приоритет, чем параметры на уровне базы данных или те параметры, которые задаются с помощью инструкции SET.

Параметры конфигурации SQL Server настроены таким образом, что их изменять следует только в самых крайних случаях, причем это нужно делать осознано, т.е. понимать, на что это повлияет, поэтому рекомендуется, чтобы такие параметры изменяли опытные администраторы или разработчики.

Некоторые часто используемые параметры конфигурации доступны в графической среде SQL Server Management Studio, все параметры посмотреть или изменить можно с помощью системной процедуры sp_configure.

Популярные параметры конфигурации сервера:

• show advanced options – параметр используется для отображения всех параметров, которые возвращает системная процедура sp_configure. По умолчанию отображается только часть параметров;
• Agent XPs – параметр включает расширенные хранимые процедуры агента SQL Server;
• clr enabled – параметр включает возможность использования пользовательских сборок CLR;
• Database Mail XPs – параметр включает компонент Database Mail на SQL сервере;
• xp_cmdshell – данный параметр позволяет включить возможность использования расширенной системной процедуры xp_cmdshell. В целях безопасности по умолчанию она отключена;
• Ad Hoc Distributed Queries – разрешает использовать нерегламентированные распределенные запросы в инструкциях OPENROWSET и OPENDATASOURCE;
• query wait – с помощью данного параметра можно задать время в секундах, в течение которого запрос будет ожидать ресурсы;
• remote login timeout — с помощью этого параметра можно изменить время ожидания в секундах подтверждения удаленного входа, по истечении которого будет возвращаться сообщение об ошибке. Например, если сервер недоступен, можно сделать так, что ошибка будет возвращаться быстрей, чем поведение по умолчанию.

Подробней обо всех параметрах конфигурации Microsoft SQL Server можете почитать в официальной справке.

Системная процедура sp_configure

sp_configure – это системная процедура, предназначенная для отображения или изменения глобальных параметров конфигурации текущего SQL сервера. После того, как Вы изменили параметр конфигурации, необходимо выполнить инструкцию RECONFIGURE, для того чтобы изменения вступили в силу, иногда, при изменении некоторых параметров, требуется перезапуск всего экземпляра SQL Server.

Если Вы укажете конфигурационное значение, которое не соответствует спецификации, т.е., например, выходит за пределы допустимых значений, RECONFIGURE выдаст ошибку (например, ошибка «Нерегламентированное обновление в системных каталогах не поддерживается»). Однако инструкция RECONFIGURE WITH OVERRIDE отключает проверку конфигурационных значений, но, как Вы понимаете, ее нужно использовать с особой осторожностью, ведь указав значение, которое выходит за пределы допустимых, Вы намерено допускаете возможность возникновения ошибок.

У процедуры sp_configure есть два необязательных параметра, это:

• Первый — имя параметра конфигурации, если параметр отсутствует, то возвращается весь список параметров;
• Второй — новое значение параметра конфигурации.

Для того чтобы выполнить процедуру sp_configure без изменений параметров конфигурации, т.е. на просмотр параметров, особые права не нужны, но, для того чтобы изменить параметры конфигурации с помощью процедуры sp_configure, нужно разрешение ALTER SETTINGS, т.е., например, быть членом предопределенных ролей сервера sysadmin и serveradmin, у которых есть данное разрешение.

Примеры использования процедуры sp_configure

Включаем отображение всех параметров конфигурации

Запускаем процедуру на просмотр параметров

Также посмотреть параметры конфигурации сервера можно с помощью системного представления sys.configurations, оно даже возвращает чуть больше информации, чем процедура sp_configure.

Разрешаем использование нерегламентированные распределенные запросы

Уменьшаем время ожидание подтверждения удаленного входа

Заметка! Если Вас интересует язык T-SQL, то рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно, с большим количеством примеров, рассказываю практически про все возможности языка Transact-SQL.

У меня все, надеюсь, материал был Вам полезен, удачи!

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

По умолчанию SQL Server SQL Server не разрешает нерегламентированные распределенные запросы, использующие операторы OPENROWSET и OPENDATASOURCE. By default, SQL Server SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. Если этот параметр равен 1, SQL Server SQL Server допускает выполнение нерегламентированных распределенных запросов. When this option is set to 1, SQL Server SQL Server allows ad hoc access. Если этот параметр не задан или равен 0, SQL Server SQL Server не разрешает нерегламентированный доступ. When this option is not set or is set to 0, SQL Server SQL Server does not allow ad hoc access.

В нерегламентированных распределенных запросах с помощью функций OPENROWSET и OPENDATASOURCE осуществляется подключение к удаленным источникам данных, использующим OLE DB. Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. Функции OPENROWSET и OPENDATASOURCE должны использоваться с теми источниками данных OLE DB, обращения к которым происходят нечасто. OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. Для источников данных, к которым обращение производится более чем несколько раз, определите связанный сервер. For any data sources that will be accessed more than several times, define a linked server.

Разрешение использования нерегламентированных имен означает, что любой пользователь, прошедший проверку подлинности при входе в SQL Server SQL Server , будет иметь доступ к поставщику. Enabling the use of ad hoc names means that any authenticated login to SQL Server SQL Server can access the provider. SQL Server SQL Server следует включить эту функцию для поставщиков, любой локальный доступ к которым не представляет опасности. administrators should enable this feature for providers that are safe to be accessed by any local login.

Remarks Remarks

Попытка установки нерегламентированного соединения без включенной функции Нерегламентированные распределенные запросы приведет к ошибке. Сообщение 7415, уровень 16, состояние 1, строка 1 Attempting to make an ad hoc connection with Ad Hoc Distributed Queries not enabled results in error: Msg 7415, Level 16, State 1, Line 1

Нерегламентированный доступ к поставщику OLE DB «Microsoft.ACE.OLEDB.12.0» запрещен. Ad hoc access to OLE DB provider ‘Microsoft.ACE.OLEDB.12.0’ has been denied. К данному поставщику доступ необходимо производить через связанный сервер. You must access this provider through a linked server.

Примеры Examples

Следующий пример включает распределенные нерегламентированные запросы и выполняет запрос к серверу Seattle1 с использованием функции OPENROWSET . The following example enables ad hoc distributed queries and then queries a server named Seattle1 using the OPENROWSET function.

When I run a query with OPENROWSET in SQL Server 2000 it works.

But the same query in SQL Server 2008 generates the following error:

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure