SSIS: Error deploying SSIS project: A required privilege is not held by the client

16-Lis-2016

Hmm… A required privilege is not held by the client… Jak ci czegoś brakuje, to powiedz co to jest! Niestety Integration Services Deployment Wizard nie chciał powiedieć co go boli…

Skoro brakuje jakiegoś prawa (Local Settings >> Local Policies >> User Rights Assigment) to naprawa SQL powinna pomóc. Ponieważ serwer był testowy, to naprawa była możliwa. Po naprawie serwera (Panel sterowania >> Programy i funkcje >> SQL Server > Repair), ale bez restartu deployment się udał. Super! Ale co jest przyczyną?

Sprawdziłem ponownie jakie prawa miało przypisane konto SQL Server po naprawie. Potem uruchomilem ponownie SQL serwer i IS Deployment Wizard przestał działać. Sprawdziłem więc znowu jakie prawa miało konto SQL server i… kilka ubyło! Podejrzani to:

„Replace a process level token”,”NT SERVICE\MSSQLSERVER”,”SeAssignPrimaryTokenPrivilege”

„Adjust memory quotas for a process”,”NT SERVICE\MSSQLSERVER”,”SeIncreaseQuotaPrivilege”

„Bypass traverse checking”,”NT SERVICE\MSSQLSERVER”,”SeChangeNotifyPrivilege”

W tym przypadku prawa są definiowane na pozioie zasad grupy, więc w kolejnym kroku była potrzebna pomoc chłopaków z AD.

Rzeczywiście te prawa są wymienione na liście wymaganych przez SQL (https://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx#Windows), więc nie powinno być problemu z ich uzyskaniem.

Często dodaję jeszcze te dwa prawa:

“Perform volume manintanace taksks”, „NT SERVICE\MSSQLSERVER” ,”SeManageVolumePrivilege”

“Lock pages in memory”, „NT SERVICE\MSSQLSERVER”,”SeLockMemoryPrivilege”

do kompletu dodam jeszcze obowiązkowo potrzebne:

„Log on as a service”, „NT SERVICE\MSSQLSERVER”,”SeServiceLogonRight”

Podczas porównywania uprawnień skorzystałem z następującego skryptu powershell, który ładnie wylistował mi użytkowników i ich prawa (skrypt po przeróbkach pochodzi z http://stackoverflow.com/questions/35344825/powershell-export-user-rights-assignment i jest super!):

#requires -version 2

# Fail script if we can't find SecEdit.exe
$SecEdit = Join-Path ([Environment]::GetFolderPath([Environment+SpecialFolder]::System)) "SecEdit.exe"
if ( -not (Test-Path $SecEdit) ) {
 Write-Error "File not found - '$SecEdit'" -Category ObjectNotFound
 exit
}

# LookupPrivilegeDisplayName Win32 API doesn't resolve logon right display
# names, so use this hashtable
$UserLogonRights = @{
 "SeBatchLogonRight" = "Log on as a batch job"
 "SeDenyBatchLogonRight" = "Deny log on as a batch job"
 "SeDenyInteractiveLogonRight" = "Deny log on locally"
 "SeDenyNetworkLogonRight" = "Deny access to this computer from the network"
 "SeDenyRemoteInteractiveLogonRight" = "Deny log on through Remote Desktop Services"
 "SeDenyServiceLogonRight" = "Deny log on as a service"
 "SeInteractiveLogonRight" = "Allow log on locally"
 "SeNetworkLogonRight" = "Access this computer from the network"
 "SeRemoteInteractiveLogonRight" = "Allow log on through Remote Desktop Services"
 "SeServiceLogonRight" = "Log on as a service"
}

# Create type to invoke LookupPrivilegeDisplayName Win32 API
$Win32APISignature = @'
[DllImport("advapi32.dll", SetLastError=true)]
public static extern bool LookupPrivilegeDisplayName(
 string systemName,
 string privilegeName,
 System.Text.StringBuilder displayName,
 ref uint cbDisplayName,
 out uint languageId
);
'@
$AdvApi32 = Add-Type advapi32 $Win32APISignature -Namespace LookupPrivilegeDisplayName -PassThru

# Use LookupPrivilegeDisplayName Win32 API to get display name of privilege
# (except for user logon rights)
function Get-PrivilegeDisplayName {
 param(
 [String] $name
 )
 $displayNameSB = New-Object System.Text.StringBuilder 1024
 $languageId = 0
 $ok = $AdvApi32::LookupPrivilegeDisplayName($null, $name, $displayNameSB, [Ref] $displayNameSB.Capacity, [Ref] $languageId)
 if ( $ok ) {
 $displayNameSB.ToString()
 }
 else {
 # Doesn't lookup logon rights, so use hashtable for that
 if ( $UserLogonRights[$name] ) {
 $UserLogonRights[$name]
 }
 else {
 $name
 }
 }
}

# Outputs list of hashtables as a PSObject
function Out-Object {
 param(
 [System.Collections.Hashtable[]] $hashData
 )
 $order = @()
 $result = @{}
 $hashData | ForEach-Object {
 $order += ($_.Keys -as [Array])[0]
 $result += $_
 }
 New-Object PSObject -Property $result | Select-Object $order
}

# Translates a SID in the form *S-1-5-... to its account name;
function Get-AccountName {
 param(
 [String] $principal
 )
 if ( $principal[0] -eq "*" ) {
 $sid = New-Object System.Security.Principal.SecurityIdentifier($principal.Substring(1))
 try
 {
 $sid.Translate([Security.Principal.NTAccount])
 }
 catch
 {
 $sid
 }
 }
 else {
 $principal
 }
}
$list = @()

$TemplateFilename = Join-Path ([IO.Path]::GetTempPath()) ([IO.Path]::GetRandomFileName())
$LogFilename = Join-Path ([IO.Path]::GetTempPath()) ([IO.Path]::GetRandomFileName())
$StdOut = &amp; $SecEdit /export /cfg $TemplateFilename /areas USER_RIGHTS /log $LogFilename
if ( $LASTEXITCODE -eq 0 ) {
 Select-String '^(Se\S+) = (\S+)' $TemplateFilename | Foreach-Object {
 $Privilege = $_.Matches[0].Groups[1].Value
 $Principals = $_.Matches[0].Groups[2].Value -split ','
 foreach ( $Principal in $Principals ) {
 $hash = @{"Privilege" = $Privilege; "PrivilegeName" = Get-PrivilegeDisplayName $Privilege; "Principal" = Get-AccountName $Principal}
 $obj = New-Object -TypeName PSObject -Property $hash
 $list+= $obj
 }
 }
}
else {
 $OFS = ""
 Write-Error "$StdOut"
}
Remove-Item $TemplateFilename,$LogFilename -ErrorAction SilentlyContinue

$list
# http://stackoverflow.com/questions/35344825/powershell-export-user-rights-assignment
# secedit /export /areas USER_RIGHTS /cfg d:\policies2.txt

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

#requires -version 2

# Fail script if we can't find SecEdit.exe

$SecEdit = Join-Path ([Environment]::GetFolderPath([Environment+SpecialFolder]::System)) "SecEdit.exe"

if ( -not (Test-Path $SecEdit) ) {

Write-Error "File not found - '$SecEdit'" -Category ObjectNotFound

exit

}

# LookupPrivilegeDisplayName Win32 API doesn't resolve logon right display

# names, so use this hashtable

$UserLogonRights = @{

"SeBatchLogonRight" = "Log on as a batch job"

"SeDenyBatchLogonRight" = "Deny log on as a batch job"

"SeDenyInteractiveLogonRight" = "Deny log on locally"

"SeDenyNetworkLogonRight" = "Deny access to this computer from the network"

"SeDenyRemoteInteractiveLogonRight" = "Deny log on through Remote Desktop Services"

"SeDenyServiceLogonRight" = "Deny log on as a service"

"SeInteractiveLogonRight" = "Allow log on locally"

"SeNetworkLogonRight" = "Access this computer from the network"

"SeRemoteInteractiveLogonRight" = "Allow log on through Remote Desktop Services"

"SeServiceLogonRight" = "Log on as a service"

}

# Create type to invoke LookupPrivilegeDisplayName Win32 API

$Win32APISignature = @'

[DllImport("advapi32.dll", SetLastError=true)]

public static extern bool LookupPrivilegeDisplayName(

string systemName,

string privilegeName,

System.Text.StringBuilder displayName,

ref uint cbDisplayName,

out uint languageId

);

$AdvApi32 = Add-Type advapi32 $Win32APISignature -Namespace LookupPrivilegeDisplayName -PassThru

# Use LookupPrivilegeDisplayName Win32 API to get display name of privilege

# (except for user logon rights)

function Get-PrivilegeDisplayName {

param(

[String] $name

)

$displayNameSB = New-Object System.Text.StringBuilder 1024

$languageId = 0

$ok = $AdvApi32::LookupPrivilegeDisplayName($null, $name, $displayNameSB, [Ref] $displayNameSB.Capacity, [Ref] $languageId)

if ( $ok ) {

$displayNameSB.ToString()

}

else {

# Doesn't lookup logon rights, so use hashtable for that

if ( $UserLogonRights[$name] ) {

$UserLogonRights[$name]

}

else {

$name

}

# Outputs list of hashtables as a PSObject

function Out-Object {

param(

[System.Collections.Hashtable[]] $hashData

)

$order = @()

$result = @{}

$hashData | ForEach-Object {

$order += ($_.Keys -as [Array])[0]

$result += $_

}

New-Object PSObject -Property $result | Select-Object $order

}

# Translates a SID in the form *S-1-5-... to its account name;

function Get-AccountName {

param(

[String] $principal

)

if ( $principal[0] -eq "*" ) {

$sid = New-Object System.Security.Principal.SecurityIdentifier($principal.Substring(1))

try

{

$sid.Translate([Security.Principal.NTAccount])

}

catch

{

$sid

}

else {

$principal

}

$list = @()

$TemplateFilename = Join-Path ([IO.Path]::GetTempPath()) ([IO.Path]::GetRandomFileName())

$LogFilename = Join-Path ([IO.Path]::GetTempPath()) ([IO.Path]::GetRandomFileName())

$StdOut = & $SecEdit /export /cfg $TemplateFilename /areas USER_RIGHTS /log $LogFilename

if ( $LASTEXITCODE -eq 0 ) {

Select-String '^(Se\S+) = (\S+)' $TemplateFilename | Foreach-Object {

$Privilege = $_.Matches[0].Groups[1].Value

$Principals = $_.Matches[0].Groups[2].Value -split ','

foreach ( $Principal in $Principals ) {

$hash = @{"Privilege" = $Privilege; "PrivilegeName" = Get-PrivilegeDisplayName $Privilege; "Principal" = Get-AccountName $Principal}

$obj = New-Object -TypeName PSObject -Property $hash

$list+= $obj

}

else {

$OFS = ""

Write-Error "$StdOut"

}

Remove-Item $TemplateFilename,$LogFilename -ErrorAction SilentlyContinue

$list

# http://stackoverflow.com/questions/35344825/powershell-export-user-rights-assignment

# secedit /export /areas USER_RIGHTS /cfg d:\policies2.txt

Podobne

Komentarze są wyłączone

Autor: Rafał Kraik

Komentarze (0)

SSIS: Error deploying SSIS project: A required privilege is not held by the client

Podobne

Szukaj

Kursy

O tym piszemy

Archiwum