CRM 2011 Installation Error – SQL Server is unavailable


While working on a routine CRM 2011 installation, all system checks passed except for Microsoft SQL Server’s. It failed with “The SQL Server ‘Server Name’ is unavailable. Gut instinct was firewall issues here.CRMInstall_SQLServerUnavailable

 

 

 

 

 

 

 

 

 

 

 

 

Disabled Windows Firewall on SQL an CRM servers but still received the same error message. Error logs did not provide much to go on here. Ports and connectivity all checked out fine – even added the config database into SQL manually and the system checks were able to determine that the database already existed, throwing the error for that (then deleted it).

Thinking the error may be a bogus one, I set the Ignore Checks key in the registry to bypass the error. This let the installation proceed but ended up failing during the creation of the organization database (e.g XXX_MSCRM). CRM itself installed, created the MSCRM_CONFIG database, and I was able to pull up the Deployment Manager to see the organization state as ‘Failed’. This was the confusing part – how was one database created but the other failed? Rechecked the error log on the installation and was provided with:

Error| Install exception.System.Exception: Action Microsoft.Crm.Tools.Admin.CreateDatabaseAction failed. —> System.IO.IOException: The network path was not found.

After uninstalling CRM and doing a little research on this, it seems similar errors are thrown when drives are not shared appropriately. On the SQL server, I opened a command prompt to run a net share on the drive and was rejected with the “The Server service is not started.” When prompted to start it, system error 1508 was shot back stating that Server service was disabled.WindowsServerService

Opened up services, went down to the Server service and did see that it was disabled. Enabled the service and started it, and ran the system checks for the CRM installation again.ServerService

Voila! All green. Installation proceeded normally and finished successfully.

Thanks to Gage Pennisi, my fellow Tribridge Co-Worker for spending the hours to determine what the issue was. This issue does not happen on a fresh install of the OS, following best practice. The customer’s servers were built from an “image” that had the service turned off.

 


Transparent Data Encryption in Microsoft Dynamics CRM 2011/(4.0)


The use of SQL TDE – Transparent Data Encryption is a great way to secure your Microsoft Dynamics CRM 2011 or 4.0 systems, especially when they contain sensitive data like credit cards or social security numbers. This ensures that even if someone gets a copy of your database, it cannot be used or restored on another system without a key.

Additionally because it’s TRANSPARENT, your CRM server or applications that access SQL server are NOT effected. That’s right no code changes etc. It is still best practice to test this out in your development environment first.

Please note SQL Server 2008 Enterprise is required in order to enabled SQL Transparent Data Encryption.






































First, let me stress the importance of saving the encrypted backup key and password, and not just a copy on the SQL server. Please take the time to store these keys in TFS, One-Note, Sharepoint, Outlook etc something that you can retrieve these items should they be needed. A customer had this stored on the local C drive of the SQL server and lost the entire server. They could not recover their databases as they did not know the password.

Setting UP TDE – Super Fast Only 10 minutes!

Step 1: From SQL Management Studio, on the MASTER DB
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘PASSWORDHERE’

Step 2: Create the TDE Certificate
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’

Step 3: Backup the Certificate
BACKUP CERTIFICATE tdeCert
TO FILE=’D:\Scripts\tdeCert.certbak’
WITH PRIVATE KEY (
FILE=’D:\Scripts\tdeCert.pkbak’,
ENCRYPTION BY PASSWORD=’PasswordHere’)

NOTE: Make sure you save the password and certificate OFF the SQL server. The D:\drive selected is a drive on the SQL Server directly.

Step 4: Select the CRM Database you want to use
USE CRM_MSCRM
GO

Step 5: Create the Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert

Step 6: Alter the DB
ALTER DATABASE CRM_MSCRM SET ENCRYPTION ON

Congratulations! You just enabled TDE! on your CRM Database!

How to Check for TDE Encryption:
SELECT DB_NAME (DATABASE_ID), encryption_state
FROM SYS.dm_database_encryption_keys

You may also read the Microsoft document covering SQL Server 2008 TDE right here:
http://msdn.microsoft.com/en-us/library/cc278098.aspx


CRM 2011 Installation Error: SQL instance name must be the same as computer name


This error is common when the SQL instance name is different the computer name.


Open SQL management studio, and issue an SP_HELPSERVER. This will return the current SQL instance name which is most likely different than the machine name. Verify the correct machine, by right clicking on computer, properties and looking under Computer Name, Domain and Work Group Settings. Computer name is the name you need to match.

Now issue SP_DROPSERVER “SERVER NAME” – Server name being the name from the SP_HELPSERVER command.

Next, issue SP_ADDSERVER “SERVER NAME”, local – Server name being the comtpuer name from computer settings.

Next, issue SP_HELPSERVER to see the name is now correct.

Finally, restart the SQL Services for MSSQLServer. Now go back to the CRM install, and recheck the condition!


Migrating CRM 2011 Online databases to SQL OnPremise: Database cannot be started in this edition of SQL Server


When Microsoft Dynamics CRM 2011 is installed using a Microsoft SQL Server 2008 Enterprise edition (CRM Online) , a partition is created for the auditing functionality of Dynamics CRM 2011.

The AuditBase table uses partitioning which is only available for Microsoft SQL Server 2008 Enterprise. This also holds true for database backups received from Microsoft’s Data Center. This will give an error: Database cannot be started in this edition of SQL Server when trying to restore the database to a Standard Version of SQL Server 2008.

The fix can be complicated without a SQL 2008 Enterprise Server readily available. Instead, use a VM and upgrade that SQL to the Enterprise Edition. Then import the database you received from
the Microsoft Data Center. Next, run the script on the database (from the KB Below) to remove SQL 2008 Enterprise Edition features.

Now, you can copy this database back to your SQL Server 2008 standard edition and restore it for use with on premise CRM.

http://support.microsoft.com/kb/2567984