Monday, April 13, 2009

Changing Default SQL Port

In order to keep data more secure requests have come in to have SQL run on a different port. This helps to avoid typical SQL port scans and keep your database off of the "easy to target" list. Changing the database and having CRM work after the change is actually pretty simple.

Please note that this configuration change requires modifying of the registry and updating the MSCRM_Config database. Both of these actions are NOT SUPPORTED by MS Support. So, perform at your own risk.

Here's how it works:

1.Open SQL Server Configuration Manager


2. Choose your instance of SQL that you want to change the port on and open the "TCP/IP" Property.


3. A new window will open.


4. Click on the "IP Addresses" tab and scroll to the bottom. Under the "IPAll" section change the "TCP Port" to the port desired. Click Ok.


You will be told that the changes will not take effect until SQL services have been restarted. Restart the Services and you are all set for this step.

Next you will need to change three keys in the MSCRM hive of the registry on the CRM server:
1. configdb
2. database
3. metabase

Each of these keys will have a "Data Source" switch that has the name of the SQL server as the Data Source. In order to tell the connection to use the specified port you will need to add a comma "," and then the port. For example:

Provider=SQLOLEDB;Data Source=SQLSERVER,1234;Initial Catalog=CompanyName_MSCRM;Integrated Security=SSPI

Lastly, you will need to update the Organization table of the MSCRM_Config DB. Just like you updated the registry keys previously, the same thing will need to be done to the "ConnectionString" column of the Organization table. Run the following SQL scripts:

SELECT ConnectionString, Id from Organization

This will give you the current connection string being used. Copy and paste the value into your query page. Add the comma "," to the Data Source and then update the table.

UPDATE Organization
SET ConnectionString = 'Provider=SQLOLEDB;Data Source=SQLSERVER,1234;Initial Catalog=CompanyName_MSCRM;Integrated Security=SSPI'
where Id = '(put in the Organization Id from your select that you ran previously)'

And now, as long as your Window's Firewall is set up to allow traffic over the newly chosen port, you're all set. Watch out for those firewall rules as they can make a perfectly implemented configuration change all for not.

Enjoy your newly secured solution!

David Fronk
Dynamic Methods Inc.

No comments:

Post a Comment