locked
CREATE MASTER KEY command hangs in SQL Management Studio (SQL 2008 R2) RRS feed

  • Question

  • Within SQL Management Studio for 2008 R2 (10.50.1600.1), I am creating a new database with default options (Right-click "Databases -> New Database").  After it has been successfully created, I am trying to run the following script (password removed) against the newly created database:

    IF NOT EXISTS ( SELECT * FROM sys.key_encryptions )<br/>
    BEGIN<br/>
     CREATE MASTER KEY ENCRYPTION BY PASSWORD = '.......'<br/>
    END
    

    The command will just sit there and say "Executing...." for an indefinite amount of time.  We let it run for 1 hour once.  The following steps have been taken but still no luck.

    • Restart SQL Server then run the command
    • Delete and re-add the database and run the command
    • Run the command logged into SQL both as a windows user with sysadmin rights and as a SQL user with dbowner rights to that database
    • Restart the entire server and then re-run the command
    • Run the command with the 'sqlcmd' tool instead of from SQL Management Studio.  Still doesn't return.
    • Closed "Object Explorer" in SQL Management Studio and then run the command

    This is happening on 2 separate virtual machines that have been restored from an image which had SQL Server 2008 installed from before.  The server name has been changed on both.  I have tested this server name change scenario though with a separate VM (and separate image) and had no problems whatsoever.  Other commands do work from the SQL query prompt.  I have confirmed that "SELECT * FROM sys.key_encryptions" returns an empty result set.

    Thanks in advance for any help, David

    Thursday, March 3, 2011 8:42 PM

Answers

  • Maybe something is blocking the command....try this:

     

    in one window in SSMS,

    SELECT @@SPID

    (note the returned number)

    Then execute your CREATE MASTER KEY statement

     

    then while it is still running open another window and execute:

    EXEC sp_who2 spid_returned_from_initial_query

    See if there is another session blocking you by inspecting the 'BlkBy' column of the output and then looking up the command running against whatever SPID that shows.

    HTH

    Ben

    Tuesday, March 8, 2011 12:16 PM

All replies

  • Anything in either the SQL Server error log or the Windows event logs?
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Friday, March 4, 2011 4:59 PM
  • Maybe something is blocking the command....try this:

     

    in one window in SSMS,

    SELECT @@SPID

    (note the returned number)

    Then execute your CREATE MASTER KEY statement

     

    then while it is still running open another window and execute:

    EXEC sp_who2 spid_returned_from_initial_query

    See if there is another session blocking you by inspecting the 'BlkBy' column of the output and then looking up the command running against whatever SPID that shows.

    HTH

    Ben

    Tuesday, March 8, 2011 12:16 PM
  • The server name has been changed on both.

    IS your SQL Server name has also changed? Is your Select @@servername and host name matches? If not then change sql server name and then restart the service and then create the key. For changing sql server name check http://msdn.microsoft.com/en-us/library/ms143799.aspx article


    Vidhya Sagar. Mark as Answer if it helps!
    Tuesday, March 8, 2011 12:52 PM