MSSQL: подслушать обмен клиентского приложения с сервером

На самом деле иногда бывает сильно интересно, какие sql-запросы посылает клиентское приложение. Если дело происходит под линуксом, через unixodbc, то там легко - приписываем в odbcinst.ini (который находится где-то то ли в /etc, то ли в /usr/local/etc) пару строк:

[ODBC]
Trace=Yes
TraceFile=/home/huh-muh/sql.log

и в своем домашнем каталоге лицезреем весь обмен. Под windows, думалось, должны быть аналогичные механизмы, но всё оказалось чуть-чуть сложнее.

Во-первых, есть хорошая статья о том, как включить трассировку для клиентов SQL Servera. Вкратце:

1. Стаскиваем и разворачиваем самораспаковывающийся архив по этой ссылке.

2. В реестре находим (или создаём) ветку

HKEY_LOCAL_MACHINE\Software\Microsoft\BidInterface\Loader

в которой создаём ключ с именем ":Path" (двоеточие тут вроде как существенно) и значением:
"%SYSTEMROOT%\Microsoft.NET\Framework\v2.0.50727\ADONETDiag.dll", если хотим трассировать ADO.NET
или
"%SYSTEMROOT%\SYSTEM32\msdaDiag.dll", если хотим трассировать прочих клиентов (mdac, dao и т.п.)

3. в кучке каталогов из п.1 находим MOF_Files и в нем выполняем команду:
mofcomp all.mof (а чего мелочиться-то)
эта команда компилирует и регистрирует трассировочные схемы (без понятия, что это такое). Результат этой регистрации можно проверить командой:
Logman query providers

4. Затем забираемся в корневой каталог этого распакованного архива и в нем выполняем команду:

Logman start MyTrace -pf "control_GUID_files/ctrl.guid.all" -o Out.etl -ets

в результате чего в этом каталоге создастся файл Out.etl, содержащий интересующую нас трассировку.
Тут есть маленькая тонкость. Если эта команда файл ctrl.guid.all не увидит (ну там, путь неправильно указали, или ещё что-то), она сильно не огорчится, но ничего ловить не будет. Поэтому надо внимательно присмотреться, пишет ли она в момент запуска раздел "Поставщики:" , в котором перечисляет guid-ы механизмов, которые будет отслеживать.

5. Запускаем приложение, которое хотим отследить, и пока мы там работаем, в файле из п.4 копится инфа в неудобоваримом виде.

6. Когда по нашему мнению наловится достаточно информации, завершаем трассировку командой:

Logman stop MyTrace -ets

7. Наконец, перегоняем информацию из этого Out.etl во что-то более текстовое командой:

TraceRPT /y Out.etl

Правда, по изучению результата трассировки меня постигло разочарование. Нет, оно там рисует строки подключения, отображает факт обмена с сервером, показывает какие-то адреса и непонятные числа, но и только. Ни одного sql-запроса в явном виде я там не обнаружил.

Этот огорчительный момент вернул меня к старому доброму подслушиванию сети. Обычно я использую Packetyzer, а тут наткнулся на Microsoft Network Monitor 3.4 и решил попробовать его. Там, правда, было написано, что нужно ещё установить дополнительно NMDecrypt, но, по-моему, он ловит всё и так.(*) Для более новых систем есть Microsoft Message Analyzer, но у меня WinXP, так что тут без вариантов. Правда, результатом подслушивания является мешанина данных в кодировке ANSI и UCS-2e, но с этим приходится мириться, главное, что хоть какие-то обрывки получаются в читабельном виде.

* на одной машине потребовалось ещё доустановить Network Monitor Parsers и выбрать в меню Tools - пунт Options - вкладка Parser profiles профиль Default, чтобы программа перестала жаловаться "unable to build conversation"

Windows: Поднимаем из бэкапов базы MSSQL на другой машине

Рассмотрим такую гипотетическую ситуацию (которая не далее как сегодня утром реализовалась на практике, но это неважно).

Есть у нас компьютер WinServA, на котором живёт-поживает и добра наживает Microsoft SQL Server 2005, хранящий все свои базы в каталоге E:\MSSQL\Data. И вот в один не очень прекрасный день этот сервер умирает. Скажем, отказали диски. Ага, сразу все. Возникает задача - поднять базу на другом, девственно чистом сервере WinServB, который до этого спокойно управлялся ОС Windows 2008, ни сном, ни духом не ведал о каких-то SQL Server-ах две тысячи пятого затёртого года и вообще, имеет свободное место только на диске D:.

Для этого нам понадобится: 1) бэкапы дорогих нашему сердцу баз, 2) бэкапы системных баз почившего сервера 3) немного валидола.

Шаг первый - поднимаем Microsoft SQL Server 2005 на Windows 2008.

Тут возможны всяческие чудеса. Например, однажды MSSQL отказывался ставиться до тех пор, пока система не притворилась, что у неё один процессор. Сегодня же нас порадовали другой ошибкой - в процессе установки MSSQL Native Client вываливается сообщение Error 1603, а файлы логов в папке C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG содержат примерно такое разъяснение:
An error occurred during the installation of assembly 'Microsoft.VC80.CRT,type="win32",version="8.0.50727.42",publicKeyToken="1fc8b3b9a1e18e3b",processorArchitecture="x86"'. Please refer to Help and Support for more information. HRESULT: 0x80070BC9. assembly interface: IAssemblyCacheItem, function: Commit, component: {98CB24AD-52FB-DB5F-A01F-C8B3B9A1E18E}
Решением оказалось в реестре прописать параметр:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control 
Key: RegistrySizeLimit 
Type: REG_DWORD 
Value: 0xffffff (4294967295)

перезагрузиться и прогнать инсталляцию ещё раз.

Дальше интереснее. Для второго шага требуется, чтобы версии старого и нового SQL Server-ов совпадали. Умерший сервер был версии 9.0.5057. Новый, после установки SP4 - 9.0.5000. Очевидно, на старый сервер накатывались какие-то обновления Центром обновлений. Но какие? Тут приходит на помощь вот эта таблица. В частности, в нашем случае помогает установка kb2494120. И можно переходить к следующему шагу.

Шаг второй - восстанавливаем системные базы из бэкапов.

На самом деле хватает трёх бэкапов: master, msdb и model. Но ситуация осложняется тем, что отличаются пути, по которым жили базы на старом сервере и на новом. Помолясь, поступаем так:
a) Находим в списке сервисов сервис MSSQLSERVER и останавливаем его. В свойствах сервиса прописываем параметр командной строки -m и запускаем сервис снова. После этого СУБД стартует в однопользовательском режиме.
b) Заходим в Microsoft SQL Server Management Studio, оно предложит законнектиться к какой-нибудь базе, мы отказываемся. Нажимаем кнопку "New Query" и пишем следующее:

RESTORE DATABASE master FROM DISK='c:\temp\master.bak' WITH REPLACE

Нажимаем конпку "Execute", указываем наконец, к какой базе прицепиться, и ждём.
Сервер сообщит, что база данных восстановлена, и работа будет продолжена после перезапуска сервиса. Фигушки, с восстановленной базой сервис запускаться откажется, указав примерно такую причину в системном журнале Windows: Could not open file E:\MSSQL\Data\mssqlsystemresource.mdf
c) Тут самое время запустить SQL Server из командной строки примерно такой командой:

"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" -f -T3608

Сервер снова станет доступным, и тогда через Microsoft SQL Server Management Studio удастся сменить пути для всех системных баз на их реальное местоположение в новом сервере:

ALTER DATABASE mssqlsystemresource modify file (name=data,filename='D:\MSSQL\Data\mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource modify file (name=log,filename='D:\MSSQL\Data\mssqlsystemresource.ldf')
ALTER DATABASE msdb modify file (name=MSDBData,filename='D:\MSSQL\Data\msdbdata.mdf')
ALTER DATABASE msdb modify file (name=MSDBLog, filename='D:\MSSQL\Data\msdblog.ldf')
ALTER DATABASE model modify file (name=modeldev,filename='D:\MSSQL\Data\model.mdf')
ALTER DATABASE model modify file (name=modellog, filename='D:\MSSQL\Data\modellog.ldf')
ALTER DATABASE tempdb modify file (name=tempdev,filename='D:\MSSQL\Data\tempdb.mdf')
ALTER DATABASE tempdb modify file (name=templog, filename='D:\MSSQL\Data\templog.ldf')

Кстати, есть способ посмотреть, какие пути каким базам назначены, выполнив команду:

SELECT name, physical_name
FROM sys.master_files

d) После этого можно попробовать вновь запустить штатно сервис MSSQLSERVER и восстановить оставшиеся системные базы:

RESTORE DATABASE msdb FROM DISK='C:\temp\msdb.bak' WITH REPLACE
RESTORE DATABASE model FROM DISK='C:\temp\model.bak' WITH REPLACE

По завершении этой процедуры у нас в руках оказывается живой сервер с восстановленными системными базами.

Шаг третий - восстанавливаем из бэкапов интересующие нас базы.

Это уже делается тривиально, со штатным использованием соответствующих пунктов контекстного меню Microsoft SQL Server Management Studio. Единственное - каждую базу придётся перед восстановлением из бэкапа пересоздать.

Шаг четвертый - пытаемся запустить SQL Server Agent.

Если тот не хочет стартовать с ошибкой Error creating a new session, значит, что-то не слава богу с правами. Нам помогло следующее:
a) на сервере выполнили команды:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

b) В группу пользователей SQLServer2005SQLAgentUser$ServerName добавили учетную запись, под которой сервис этого самого агента у нас запускается. Не знаем точно, что из этого помогло, но больше SQL Server Agent нам не жаловался.

Шаг пятый - выставляем правильное имя сервера в планах обслуживания.

Если были настроены какие-то планы обслуживания, то в них нужно поменять свойства локальных подключений (Local connection). Сделать это можно либо через редактирование планов в Microsoft SQL Server Management Studio (кнопка Manage connections), либо выполнив вот такой скрипт:

use msdb

-- указываем имя старого сервера
DECLARE @oldservername as varchar(max)
SET @oldservername='WinServA'

-- указываем имя нового сервера
declare @newservername as varchar(max)
set @newservername='WinServB'

declare
    @planid      uniqueidentifier,
    @xml         varchar(max),
    @planname    varchar(max)

-- вытаскиваем все планы, в строке подключения которых значится старый сервер
DECLARE PlansToFix LOCAL STATIC CURSOR FOR
SELECT
    id
FROM sysdtspackages90
WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

OPEN PlansToFix
FETCH NEXT FROM PlansToFix INTO @planid

WHILE (@@fetch_status != -1)
    BEGIN

    if (@@fetch_status != -2)
        begin

        -- получаем имя плана и его содержимое в виде xml-строки
        select
            @xml = cast(cast(packagedata as varbinary(max)) as varchar(max)),
            @planname = name
        from sysdtspackages90
        where id = @planid

        -- печатаем пользователю, какой план патчим
        print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername

        -- меняем упоминания старого сервера на новый
        set @xml = replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')

        -- обновляем план
        UPDATE sysdtspackages90
        SET packagedata = cast(@xml as varbinary(max))
        WHERE id = @planid

        end

    FETCH NEXT FROM PlansToFix INTO @planid  

    END

CLOSE PlansToFix
DEALLOCATE PlansToFix

Вот, собственно, и всё.

MSSQL: Функция для транслита

Попытался тут давеча использовать SOUNDEX и DIFFERENCE для поиска похожих фамилий. Безуспешно - выяснилось, что эти функции с кириллицей не работают. Возникла идея транслитерировать фамилии и уже затем скармливать их этим функциям. Эксперимент показал, что всё равно получается не очень хорошо, так что идея умерла. А функция транслитерации (по стандарту ГОСТ Р 52535.1-2006) осталась, вот:

CREATE FUNCTION TranslitString(@string varchar(256)) RETURNS varchar(256)
AS BEGIN

DECLARE @return varchar(256)

SET @return = UPPER(@string)
SET @return = REPLACE(@return, 'АЙ', 'AY')
SET @return = REPLACE(@return, 'ЕЙ', 'EY')
SET @return = REPLACE(@return, 'ИЙ', 'IY')
SET @return = REPLACE(@return, 'ОЙ', 'OY')
SET @return = REPLACE(@return, 'УЙ', 'UY')
SET @return = REPLACE(@return, 'ЫЙ', 'Y')
SET @return = REPLACE(@return, 'ЭЙ', 'EY')
SET @return = REPLACE(@return, 'ЮЙ', 'YUY')
SET @return = REPLACE(@return, 'ЯЙ', 'YAY')
SET @return = REPLACE(@return, 'А', 'A')
SET @return = REPLACE(@return, 'Б', 'B')
SET @return = REPLACE(@return, 'В', 'V')
SET @return = REPLACE(@return, 'Г', 'G')
SET @return = REPLACE(@return, 'Д', 'D')
SET @return = REPLACE(@return, 'Е', 'E')
SET @return = REPLACE(@return, 'Ё', 'E')
SET @return = REPLACE(@return, 'Ж', 'ZH')
SET @return = REPLACE(@return, 'З', 'Z')
SET @return = REPLACE(@return, 'И', 'I')
SET @return = REPLACE(@return, 'Й', 'I')
SET @return = REPLACE(@return, 'К', 'K')
SET @return = REPLACE(@return, 'Л', 'L')
SET @return = REPLACE(@return, 'М', 'M')
SET @return = REPLACE(@return, 'Н', 'N')
SET @return = REPLACE(@return, 'О', 'O')
SET @return = REPLACE(@return, 'П', 'P')
SET @return = REPLACE(@return, 'Р', 'R')
SET @return = REPLACE(@return, 'С', 'S')
SET @return = REPLACE(@return, 'Т', 'T')
SET @return = REPLACE(@return, 'У', 'U')
SET @return = REPLACE(@return, 'Ф', 'F')
SET @return = REPLACE(@return, 'Х', 'KH')
SET @return = REPLACE(@return, 'Ц', 'TC')
SET @return = REPLACE(@return, 'Ч', 'CH')
SET @return = REPLACE(@return, 'Ш', 'SH')
SET @return = REPLACE(@return, 'Щ', 'SHCH')
SET @return = REPLACE(@return, 'Ы', 'Y')
SET @return = REPLACE(@return, 'Э', 'E')
SET @return = REPLACE(@return, 'Ю', 'IU')
SET @return = REPLACE(@return, 'Я', 'IA')
SET @return = REPLACE(@return, 'Ь', '')
SET @return = REPLACE(@return, 'Ъ', '')

RETURN @return

END

SQL Server: Показать разрешения пользователя на объект

Оказывается, просто:

EXECUTE AS LOGIN = 'WanidaBenshoof';
SELECT * FROM fn_my_permissions('AdventureWorks2012.HumanResources.Employee', 'OBJECT') 
    ORDER BY subentity_name, permission_name ;  
REVERT;
GO

SQL Server: удалить Maintenance Plan

Почему-то просто через Management Studio удаление не срабатывало.

Нашлась вот такая последовательность действий:

1. Select the ID with the select statement
select * from sysmaintplan_plans

2. Replace with the selected ID and run the delete statements
delete from sysmaintplan_log where plan_id = ''

delete from sysmaintplan_subplans where plan_id = ''

delete from sysmaintplan_plans where id = ''

3. Delete the SQL Server Jobs with the Management Studio

SQL Server 2005 + Win2008: Login failed (Error: 18456, State: 11)

После установки SQL Server 2005 на Windows 2008 со смешанным режимом аутентификации выяснилось, что подцепиться с доменной, даже самой крутой учеткой не удается. Решение помогло такое:

create login [domain\user] from windows
go
sp_addsrvrolemember [domain\user], 'sysadmin'
go

Взаимодействие PHP и MSSQL - новые веяния

Поразительный факт: подкупающие своей привычностью функции mssql_connect, mssql_query и т.п. канули в небытие начиная с версии 5.3.0, по крайней мере в варианте для windows.

Однако, как выяснилось, Microsoft в беде не оставит! Есть вполне работоспособный драйвер версии аж 2.0.1. Для него, правда, требуется нативный клиент SQL Server 2008, но его тоже легко можно скачать по ссылке вот с этой страницы (если, конечно, удастся нужную ссылку на этой странице отыскать ^_^).

Единственная тонкость: драйвер представляет из себя набор dll-ек "на все случаи жизни", и требуется выбрать из них нужную. Перебором получилось прописать в php.ini, закинув предварительно в папку расширений соответствующий файл:

extension=php_pdo_sqlsrv_53_ts_vc9.dll

что, видимо намекает на то, что PHP 5.3.1 компилировался при помощи Visual C++ 9...

В общем, минимально рабочий код оказался таким (лёгкая вариация примера из хелповника, поставляемого вместе с драйвером):

/* Указываем параметры поключения: имя сервера, логин-пароль и базу. */
$serverName = "myserver.mydomain.ru";
$connectionInfo = array( "UID"=>"uid",
                         "PWD"=>"pwd",
                         "Database"=>"AdventureWorks");

/* Подключаемся к серверу используя аутентификацию SQL Server. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Unable to connect.";
     die( print_r( sqlsrv_errors(), true));
}

/* Выполняем SQL-запрос. */
$tsql = "SELECT getdate()";
$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
     echo "Error in executing query.";
     die( print_r( sqlsrv_errors(), true));
}

/* Получаем результаты запроса и выводим их на экран (казалось бы). */
// $row = sqlsrv_fetch_array($stmt);
// echo "Server date: ".$row[0];
// не проканало с ошибкой Object of class DateTime could not be converted to string

/* Получаем результаты запроса и выводим их на экран - а вот так проканало. */
if (sqlsrv_fetch($stmt);)
     echo "Server date: ".sqlsrv_get_field( $stmt, 0, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR) );

/* Освобождаем ресурсы, выделенные для запроса и подключения. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);

В первом приближении, правда, натолкнулся на ошибки, связанные с трудностью преобразования типа DateTime к строке (отсюда и закомментированный код), но начало положено...

как узнать версию MS SQL:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

удалось подрубиться к MSSQL из PHP

DEBIAN:

ну-у, сначала пришлось доустановить некий компонент apt-get install php5-sybase-чего-то-там (странно, при чем здесь MSSQL?), потом воспользоваться советом из хелпа:

senyahnoj
08-Feb-2005 10:09
When moving the following script from PHP on Win32 to PHP on Linux, I encountered problems:

$c = mssql_connect("SERVER\INSTANCE","UID","PWD");

After much searching, I discovered that 'instances' are just named aliases for port numbers, so on Linux this should be written:

$c = mssql_connect("SERVER:PORT","UID","PWD");

Please also note that the colon (:) should be used on Linux as the delimiter between servername and port number, not the comma (,) which only works on Win32 servers.

Windows:

Есть небольшая тонкость в php 5.1.4, а именно - мешает жить какая-то нехорошая ntwdblib.dll. Работоспособную версию можно взять отсюда.