trying to encrypt database master key with service master key using powershell

Question

I am trying to encrypt the master key in the master database of SQL Server with the Service master key using powershell.

The Master Key details are listed using SMO as below.

$instanceName = "sqlinstance"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

$databasename = "master"
$database = $server.Databases[$databasename]
$database.MasterKey

***************************************************************************
CreateDate          : 7/20/2018 5:01:25 AM
DateLastModified    : 7/20/2018 5:01:25 AM
IsEncryptedByServer : False
IsOpen              : False
Parent              : [master]
Urn                 : 
Server[@Name='sqlinstance']/Database[@Name='master']/MasterKey
Properties          : 
{Name=CreateDate/Type=System.DateTime/Writable=False/Value=07/20/2018 
05:01:25,

Name=DateLastModified/Type=System.DateTime/Writable=False/Value=07/20/2018 
05:01:25,

Name=IsEncryptedByServer/Type=System.Boolean/Writable=False/Value=False,
                  Name=IsOpen/Type=System.Boolean/Writable=False/Value=False}
ExecutionManager    : Microsoft.SqlServer.Management.Smo.ExecutionManager
UserData            :
State               : Existing
IsDesignMode        : False
***************************************************************************

The Master Key has a method, AddServiceKeyEncryption(), as mentioned here.

I am trying to use this method as below. But, it throws an error, that is below the command.

$database.MasterKey.AddServiceKeyEncryption()
***************************************************************************
Exception calling "AddServiceKeyEncryption" with "0" argument(s): "Add 
encryption failed for MasterKey
'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'. "
At line:1 char:1
+ $database.MasterKey.AddServiceKeyEncryption()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException
***************************************************************************

Can someone please help here.

Adding Full Error:

PSMessageDetails      :
Exception             : 
System.Management.Automation.MethodInvocationException: Exception calling
                    "AddServiceKeyEncryption" with "0" argument(s): "Add 
encryption failed for MasterKey
                    'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'. " --- 
>

Microsoft.SqlServer.Management.Smo.FailedOperationException: Add encryption 
failed for
                    MasterKey 
'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'.  --->

Microsoft.SqlServer.Management.Smo.InvalidSmoOperationException: You cannot 
execute this
                    operation since the object has not been created.
                       at 
Microsoft.SqlServer.Management.Smo.SqlSmoObject.CheckObjectStateImpl(Boolean
                    throwIfNotCreated)
                       at 
Microsoft.SqlServer.Management.Smo.MasterKey.AddServiceKeyEncryption()
                       --- End of inner exception stack trace ---
                       at 
Microsoft.SqlServer.Management.Smo.MasterKey.AddServiceKeyEncryption()
                       at CallSite.Target(Closure , CallSite , Object )
                       --- End of inner exception stack trace ---           
at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception
                    exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)
                       at CallSite.Target(Closure , CallSite , Object )
                       at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
                       at System.Management.Automation.Interpreter.DynamicInstruction`2.Run(InterpretedFrame frame)
                       at
                    System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame
                    frame)
TargetObject          :
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : FailedOperationException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {}

A good first step with any powershell issue is to pass the error object to select-object and see the full error - eg $Error[0] | Select-Object * — ConstantineK, Jul 25 '18 at 13:32
Thanks for the suggestion, as per the error, though I open the master key, and try calling the method, I still get the same error. — Adarsh, Jul 25 '18 at 14:02
Hm, sorry about that, it seems like the error would indicate the master key either doesnt exist (and thus creating it would help) or that opening it failed (and that it wouldnt be able to tell the difference) — ConstantineK, Jul 25 '18 at 20:04

trying to encrypt database master key with service master key using powershell

0 Answers0