A partir de SSIS 2012 tenim l'opció de treballar en package deploymen o project deploymnent. Si treballem en project deployment tenim un fitxer Project.prarams non és una bona pràctica tenir mapejades les connexions que tenim, com poden ser les de BBDD. Per temes de seguretat també és bona pràctica que les connexions siguin per Windows Authentication, sobretot un cop pujades al servidor. El que ens passa a vegades és que quan desenvolupem el nostre usuari no té permisos de Windows Authentication i ens hem de logar a la BBDD per usuari i password d'SQL Server. Quan tenim aquest cas hem de modificar a mà cadascun dels paràmetres.
Avui veurem una manera d'automatitzar la transformació del fitxer de paràmetres de Windows Authentication a login d'SQL Server i a la inversa. Per fer-ho farem servir PowerShell.
Abans de poder utilitzar PowerShell necessitarem habilitar els permisos, sinó ens pot donar un error del tipus:
./XXXXXXXXXXXX.ps1 : No se puede cargar el archivo C:\Temp\XXXXXXXXXXXX.ps1 porque la ejecución de scripts está
deshabilitada en este sistema. Para obtener más información, vea el tema about_Execution_Policies en
http://go.microsoft.com/fwlink/?LinkID=135170.
En línea: 1 Carácter: 1
+ ./XXXXXXXXXXXX.ps1
+ ~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess
Per evitar aquest error hem d'executar: Set-ExecutionPolicy Unrestricted
Aquesta comanda s'ha d'executar com a administrador en el PowerShell de 32-bits (per a que funcioni des del SSDT). Jo recomano fer-ho tant en el de 32-bits com el de 64-bits.
Un cop tenim els permisos assignats ja podem començar a fer els scripts. Farem primer un script en PowerShell per a fer la modificació del contingut del fitxer project.Params, després invocarem aquest fitxer des d'un bat per poder-li invocar desde SSDT i finalment configurarem l'excució del .bat com a external tool.
SCRIPT POWERSHELL
param (
[Parameter(Mandatory=$true)][string]$path,
[Parameter(Mandatory=$true)][string]$username,
[Parameter(Mandatory=$true)][string]$password
)
$strReplace = "User ID="+$username+";Password="+$password+";"
(Get-Content $path\Project.params) | ForEach-Object { $_ -replace "Integrated Security=SSPI;", $strReplace } | Set-Content $path\Project.params
A aquest script li passem 3 paràmetres: path on està el fitxer Project.params, username d'SQLServer i password.
El que fa l'script és agafar tots els fitxers Project.params del path indicat (només n'hi haurà 1) i per cadascun d'ells substitueix Integrated Security=SSPI; per User ID=<username>;Password=<password>. Finalment bolca el nou contingut altre cop a Project.params
Aquest és l'script per canviar de Windows Authentication a SQL Server login, per al cas invers només cal canviar la darrera fila per:
(Get-Content $path\Project.params) | ForEach-Object { $_ -replace $strReplace, "Integrated Security=SSPI;" } | Set-Content $path\Project.params
SCRIPT BAT
Suposem que el nostre script de powershell es diu: c:/temp/fromWinToUser.ps1
powershell c:/temp/fromWinToUser.ps1 %1 %2 %3 %4 %5 %6
Els paràmetres són els següents:
%1 = -path
%2 = path del fitxer Project.params
%3 = -username
%4 = username SQL Server
%5 = -password
%6 = password SQL Server
Aquest bat ens servirà per poder invocar el PowerShell a través del SSDT
EXECUCIÓ DESDE SSDT
Per executar la comanda des de SSDT anirem a Tools --> External Tools... i n'afegirem una de nova amb les següents característiques:
Title: FromWinToUser
Command: <path del bat>\fromWintoUser.bat
Arguments: -path $(ProjectDir) -username <username> -password <password>
Cal marcar el check "Prompt for arguments" i és aconsellable marcar també "Use Output Window".
Per afegir el pas fromUserToWin s'ha de fer exactament el mateix apuntant a l'altre bat.
Un cop ho tenim configurat ja ens apareix al menú de Tools. Quan ho executem ens apareix la pantalla per introduir els paràmetres. Ho modifiquem amb l'userId i password d'SQL Server i executem.
Si tot ha anat correcte SSDT ens informarà que el fitxer ha estat modificat des d'una app externa i si el volem sobreescriure.
Quan acabem hem d'executar la funció inversa per retornar les connexions a Windows Authentication.
Hem d'anar amb compte si el path on està el fitxer Project.params té espais, és possible que els paràmetres passats al bat no els agafi correctament.
