locked
HELP! My data is encrypted and I didn't do it! RRS feed

  • Question

  • Two weeks ago we upgraded CRM from 2011 to 2013 after extensive tests in our lab.

    Despite the tests we had some serious issues with async services and the e-mail router. Our upgraded server was almost unusable and, as a result, we threw up a new server and installed a fresh CRM 2013 on it. We then pointed this new server at the old database.

    As soon as we did this, encryption was enabled BUT NOBODY ENABLED IT!! As a result, I have encrypted fields in my database which prevent me from doing things like accepting/rejecting user e-mail accounts and, most pressingly, sending e-mails from within CRM.

    When I look at data encryption, there is no key. As I said, nobody turned encryption on. But it is on. When you try and send e-mail, you get an error which says that there are encrypted fields in the organization database, but the encryption feature is not activated. To activate it, I need the key, but I don't have a key since I never turned this on.

    Ideas? This is a production system - re-doing the upgrade is not an option. I need to get at my data. Looking at the data from SQL Server Management Studio, no fields appear encrypted. Why does CRM think the data is encrypted and how can I tell it it isn't?


    Question everything

    Tuesday, March 4, 2014 9:13 AM

All replies

  • This is from the CRM 2013 IG.  My guess is you don't have SSL enabled.

    1. Data encryption

    Microsoft Dynamics CRM 2013 uses standard SQL Server cell level encryption for a set of default entity attributes that contain sensitive information, such as user names and email passwords. This feature can help organizations meet FIPS 140-2 compliance.

    For Microsoft Dynamics CRM Online, all new and upgraded organizations use data encryption.

    For on-premises versions of Microsoft Dynamics CRM 2013, data encryption is not active by default for new or upgraded organizations. However, data encryption may be activated at any time.

    Microsoft Dynamics CRM  users who have the system administrator security role can activate data encryption or change the encryption key after data encryption is enabled in the Settings > Data Management > Data Encryption area. After you activate data encryption, you cannot turn it off.

    Important

    For on-premises versions of Microsoft Dynamics CRM:

    • Changing the encryption key requires SSL configured on the Microsoft Dynamics CRM website.
    • It is a best practice is to change the encryption key once every year.
    • The encryption key is required to activate data encryption when you import an organization database into a new deployment or a deployment that has had the configuration database (MSCRM_CONFIG) re-created after the organization was encrypted. You can copy the original encryption key to Notepad and paste it into the Settings > Data Management > Data Encryption dialog box after the organization import is completed. 
    • When you re-enter the data encryption key, we recommend that you run the Microsoft Dynamics CRM web application using Internet Explorer to paste the encryption key into the Data Encryption dialog box.
    1. Copy your organization data encryption key

    We strongly recommend that you make a copy of your data encryption key. This is particularly important for on-premises deployments that may need to reactivate data encryption after a redeployment or failure recovery.

    Copy an organization data encryption key

    1.     Sign in to Microsoft Dynamics CRM as a user with the system administrator security role.
    2.     Go to Settings > Data Management > Data Encryption.
    3.     In the Data Encryption dialog box, select Show Encryption Key, in the Current encryption key box select the encryption key, and copy it to the clipboard.

    Caution

    If the Microsoft Dynamics CRM website is not configured for HTTPS/SSL, the Data Encryption dialog box will not be displayed. For a more secure deployment, we recommend that you configure the website for HTTPS/SSL. However, if the website is not configured for HTTP/SSL, use a tool that can be used to modify CRM database tables, such as Microsoft SQL Server Management Studio or the Deployment Web Service, open the configuration database (MSCRM_CONFIG), and in the DeploymentProperties table, set DisableSSLCheckForEncryption to 1.

    1.     Paste the encryption key in to a text editor, such as Notepad.
    2.     As a best practice, save the text file that contains the encryption key on a computer in a secure location on an encrypted hard drive.


    Jason Peterson

    Tuesday, March 4, 2014 10:52 AM
  • Thanks, Jason, but SSL has been enabled throughout.

    Question everything

    Tuesday, March 4, 2014 10:56 AM
  • Thanks, Dynamotion. I had seen this article. (a) I have a public certificate and so cannot manage my public keys but, (b) in a standard CRM 2013 installation, the key is stored IN PLAIN TEXT in the config database: MSCRM_CONFIG.dbo.DataEncryptionKey

    In my case, I seem to have data encrypted without the key being stored.

    Question everything

    Tuesday, March 4, 2014 12:37 PM
  • I've got exactly the same situation - imported a CRM 2011 DB in CRM 2013, all appears normal but unable to send out emails or change email address of a user. Disabled the SSLCheckForEncryption setting in MSCRM_Config and got to open up the Data Encryption settings in the client which shows status as Inactive and no current key. What should we do about this? I couldn't find clue as how to retrieve the key from my 2011 source... 
    Thursday, May 1, 2014 1:01 AM
  • We did eventually solve this, but it's dirty. The encrypted fields are the password fields for various tables. Set all these fields to NULL. You can then turn on encryption using whatever key phrase you like. These fields will slowly populate again, although what they do is a mystery to me since emptying them did not affect functionality at all.

    Code:

    use CRM_MSCRM
    update EmailServerProfile set IncomingPassword = null
    update EmailServerProfile set OutgoingPassword = null
    update Mailbox set Password = null
    update Queue set EmailPassword = null
    update UserSettings set EmailPassword = null



    Question everything

    • Proposed as answer by Andrew Vogel Thursday, June 12, 2014 9:59 PM
    Thursday, May 1, 2014 6:55 AM
  • Hi,

    Did you solve this?  I have the same problem! 

    Thursday, June 12, 2014 5:05 PM
  • I did.

    The encrypted fields are all password fields. Empty all these fields and you will no longer have the issue. You can now turn encryption on and provide a password of your choosing.

    I used the following:

    use CRM_MSCRM
    update EmailServerProfile set IncomingPassword = null
    update EmailServerProfile set OutgoingPassword = null
    update Mailbox set Password = null
    update Queue set EmailPassword = null
    update UserSettings set EmailPassword = null

    Thursday, June 12, 2014 6:03 PM
  • Thanks Appollyon. This saved us. Our scenario was

    Updated CRM2011 to 2013. Encryption was inactive but when we tried to enter a new key and activate we were prompted that the entered key "doesnt match the original encryption key that was used to encrypt the data". We did not set this original key and had no way of retrieving it (having tried all the other solutions, including searching databases and accessing via code).


    Seamus

    • Proposed as answer by sg123 Thursday, July 10, 2014 3:32 PM
    Thursday, July 10, 2014 3:31 PM
  • Glad I could help.

    Question everything

    Friday, July 11, 2014 6:53 AM
  • Hi,

    Thanks Appollyon for this post. Now I'm able to activate a new key for data encryption.

    But I wonder by emptying the said fileds as mentioned by you. Does effect anything?
    Did we need to pass new passwords for said fields?

    Please suggest. Thanx in advance.    

    Wednesday, September 3, 2014 7:12 AM
  • Hi Bharti

    Ours was an upgrade to an existing 2011 installation and has been running, stable, as 2013 since early this year. During that time, the solution we discovered has not proved to have had any side-effects

    When you re-encrypt your data, you provide a password, but I'm really not sure what the other passwords were there for. They were not involved in mail synchronisation or in anything else we could establish. That said, we are using the mail router - not sure if the scenario would be different for server-side synchronisation

    Hope this helps.


    Appollyon


    • Edited by avitman Thursday, September 4, 2014 6:30 AM
    Thursday, September 4, 2014 6:29 AM