Obtaining a license key from the Microsoft Dynamics CRM database

If all of a sudden it happened that you lost your LicenseKey for MS CRM , it can peep in the database MS SQL.

It should do the following query to your database MS CRM :



SELECT * from License
SELECT LicenseKey from License




select * from sys.databases


"Contains one row per database in the instance of Microsoft SQL Server."

select * from sys.databases; 

If the caller of sys.databases is not the owner of the database and the database is not master
or tempdb, the minimum permissions required to see the corresponding row are
ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission,
or CREATE DATABASE permission in the master database. If a database is not ONLINE
or AUTO_CLOSE is set to ON, the values of some columns may be NULL. If a database is
OFFLINE, the corresponding row is not visible to low-privileged users.
To see the corresponding row if the database is OFFLINE, a user must have at least the
ALTER ANY DATABASE server-level permission or the CREATE DATABASE permission
in the master database.

name
Name of database, unique within an instance of SQL Server.

database_id
ID of the database, unique within an instance of SQL Server.

source_database_id
Non-NULL = ID of the source database of this database snapshot.

owner_sid
SID (Security-Identifier) of the external owner of the database, as registered to the server.

create_date
Date the database was created or renamed. For tempdb, this value changes every time the server restarts.

compatibility_level
Integer corresponding to the version of SQL Server for which behavior is compatible:

collation_name
Collation for the database. Acts as the default collation in the database.

user_access
User-access setting:

user_access_desc
Description of user-access setting:

is_trustworthy_on
1 = Database has been marked trustworthy.
0 = Database has not been marked trustworthy.

is_db_chaining_on
1 = Cross-database ownership chaining is ON.
0 = Cross-database ownership chaining is OFF.

is_parameterization_forced
1 = Parameterization is FORCED.
0 = Parameterization is SIMPLE.

is_master_key_encrypted_by_server
1 = Database has an encrypted master key.
0 = Database does not have an encrypted master key.

is_read_only
1 = Database is READ_ONLY.
0 = Database is READ_WRITE.

is_auto_close_on
1 = AUTO_CLOSE is ON.
0 = AUTO_CLOSE is OFF.

is_auto_shrink_on
1 = AUTO_SHRINK is ON.
0 = AUTO_SHRINK is OFF.

state
Database state:
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE

state_desc
Description of the database state:
ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
EMERGENCY
OFFLINE

is_in_standby
Database is read-only for restore log.

is_cleanly_shutdown
1 = Database shutdown cleanly; no recovery required on startup.
0 = Database did not shutdown cleanly; recovery is required on startup.

snapshot_isolation_state
State of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
0 = Snapshot isolation state is OFF (default). Snapshot isolation is disallowed.
1 = Snapshot isolation state ON. Snapshot isolation is allowed.
2 = Snapshot isolation state is in transition to OFF state. All transactions have their modifications versioned. Cannot start new transactions using snapshot isolation. The database remains in the transition to OFF state until all transactions that were active when ALTER DATABASE was run can be completed.
3 = Snapshot isolation state is in transition to ON state. New transactions have their modifications versioned. Transactions cannot use snapshot isolation until the snapshot isolation state becomes 1 (ON). The database remains in the transition to ON state until all update transactions that were active when ALTER DATABASE was run can be completed.

snapshot_isolation_state_desc
Description of state of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
OFF
ON
IN_TRANSITION_TO_ON
IN_TRANSITION_TO_OFF



is_read_committed_snapshot_on
1 = Read-committed-snapshot option is ON. Read operations under the read-committed isolation level are based on snapshot scans and do not acquire locks.
0 = Read-committed-snapshot option is OFF (default). Read operations under the read-committed isolation level use share locks.

recovery_model
Recovery model selected:
1 = FULL
2 = BULK_LOGGED
3 = SIMPLE

recovery_model_desc
Description of recovery model selected:
FULL
BULK_LOGGED
SIMPLE

page_verify_option
Setting of PAGE_VERIFY option:
0 = NONE
1 = TORN_PAGE_DETECTION
2 = CHECKSUM

page_verify_option_desc
Description of PAGE_VERIFY option setting:
NONE.TORN_PAGE_DETECTION
CHECKSUM

is_date_correlation_on
1 = DATE_CORRELATION_OPTIMIZATION is ON.
0 = DATE_CORRELATION_OPTIMIZATION is OFF.

is_auto_create_stats_on
1 = AUTO_CREATE_STATISTICS is ON.
0 = AUTO_CREATE_STATISTICS is OFF.

is_auto_update_stats_on
1 = AUTO_UPDATE_STATISTICS is ON.
0 = AUTO_UPDATE_STATISTICS is OFF.

is_auto_update_stats_async_on
1 = AUTO_UPDATE_STATISTICS_ASYNC is ON.
0 = AUTO_UPDATE_STATISTICS_ASYNC is OFF.

is_ansi_null_default_on
1 = ANSI_NULL_DEFAULT is ON.
0 = ANSI_NULL_DEFAULT is OFF.

is_ansi_nulls_on
1 = ANSI_NULLS is ON.
0 = ANSI_NULLS is OFF.

is_ansi_padding_on
1 = ANSI_PADDING is ON.
0 = ANSI_PADDING is OFF.

is_ansi_warnings_on
1 = ANSI_WARNINGS is ON.
0 = ANSI_WARNINGS is OFF.

is_arithabort_on
1 = ARITHABORT is ON.
0 = ARITHABORT is OFF.

is_concat_null_yields_null_on
1 = CONCAT_NULL_YIELDS_NULL is ON.
0 = CONCAT_NULL_YIELDS_NULL is OFF.

is_numeric_roundabort_on
1 = NUMERIC_ROUNDABORT is ON.
0 = NUMERIC_ROUNDABORT is OFF.

is_quoted_identifier_on
1 = QUOTED_IDENTIFIER is ON.
0 = QUOTED_IDENTIFIER is OFF.

is_recursive_triggers_on
1 = RECURSIVE_TRIGGERS is ON.
0 = RECURSIVE_TRIGGERS is OFF.

is_cursor_close_on_commit_on
1 = CURSOR_CLOSE_ON_COMMIT is ON.
0 = CURSOR_CLOSE_ON_COMMIT is OFF.

is_local_cursor_default
1 = CURSOR_DEFAULT is local.
0 = CURSOR_DEFAULT is global.

is_fulltext_enabled
1 = Full-text is enabled for the database.
0 = Full-text is disabled for the database.

is_supplemental_logging_enabled
1 = SUPPLEMENTAL_LOGGING is ON.
0 = SUPPLEMENTAL_LOGGING is OFF.

is_published
1 = Database is a publication database in a transactional or snapshot replication topology.
0 = Is not a publication database.

is_subscribed
1 = Database is a subscription database in a replication topology.
0 = Is not a subscription database.

is_merge_published
1 = Database is a publication database in a merge replication topology.
0 = Is not a publication database in a merge replication topology.

is_distributor
1 = Database is the distribution database for a replication topology.
0 = Is not the distribution database for a replication topology.

is_sync_with_backup
1 = Database is marked for replication synchronization with backup.
0 = Is not marked for replication synchronization with backup.

service_broker_guid
Identifier of the service broker for this database. Used as the broker_instance of the target in the routing table.

is_broker_enabled
1 = The broker in this database is currently sending and receiving messages.
0 = All sent messages will stay on the transmission queue and received messages will not be put on queues in this database.

By default, restored or attached databases have the broker disabled.

log_reuse_wait
Reuse of transaction log space is currently waiting on one of the following:
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
6 = Replication
7 = database snapshot Creation
8 = Log Scan
9 = Other (transient)

If the reason is LOG_BACKUP, it may take two backups to actually free the space.

log_reuse_wait_desc
Description of reuse of transaction log space is currently waiting on one of the following:
NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT






select * from sys.backup_devices

Contains a row for each backup-device registered by using sp_addumpdevice  or created in SQL Server Management Studio.

select * from sys.backup_devices;


Column names

name:  Name of the backup device. Is unique in the set.
type: Type of backup device:
type_desc: Description of backup device type:
physical_name: Physical file name or path of the backup device.

Crm 2011 Configure IFD Hosted Setup

Like many, we have struggled to configure Microsoft CRM 2011 as an Internet Facing Deployment. There is quite a bit of disjointed and some what typical Microsoft "junk" on how to set this up.

So after reading the White Papers, blogs and YouTube videos on the topic, I figured I would need notes for myself as much as anything. This is mostly because I am yet to find one single example that covered the setup I was after. That being:

Single Server

On an existing domain

Running true IFD ready for customer access.

The last point it telling, as all the Microsoft examples give a self generated SSL cert, that really is an example of a DEV environment only. We want to test the "real deal", and don’t mind spending a few $ on a real Certificate to see this in a true working environment.

The Existing Setup

Because this is a test environment, we are running the server on a Hyper V server. A single VM machine, that is running a fully patched version of:

  • Windows 2008 R2 SP1 64 Bit
  • SQL 2008 R2 64 Bit
  • Microsoft CRM 2011 64 Bit

Interesting enough, something that always takes me 15 min, it ensuring I download the correct version of the ISO files from MSDN. I get it that I am somewhat lame, but if you get a wrong version you can waste a load of time and energy later.

image

With a list looking like this it can be painful. Anyway, these are the files we used for install:

image

For those who care, the VM was set to run with 6000 MB ram, and fold out to use more.

image

Importantly

When we setup CRM, we selected the option to NOT use the default website, but configure a new one with the default settings of port 5555. This is necessary as you will see later.

Backup First

In all things Microsoft world, it is vital what you establish a working point to avoid unnecessarily installing things all over again. To get things working we have started fresh over 4 times.

Hyper V is great for this, as we just stopped the server, and made a copy of the VHD file. Then when it is time to start all over, it is just a matter of restoring from copy/backup.

Test First

Test that your CRM setup is working. Go to the local computer name (ours is VSERVER08) on the correct port: http://vserver08:5555

We called our Deployment of CRM – "CRM2011" So the URL redirects to: http://vserver08:5555/CRM2011/main.aspx

and after being prompted for login, we are in and testing.

image

Apply a Wildcard SSL Certificate

In CRM, the accessing of deployments is handled by the sub domains. So if we call a deployment "business1" we will access that as:  https://business1.domain.com

For testing, we purchased a standard Wildcard SSL certificate that applied that to the IIS7 server.

We will let you work out that bundle of joy, but a few tips.

1. Godaddy was about as cheap as you find on the net.

2. Setup involves creating a certificate request from within IIS, then pasting that text into the online providers order system. They then generate the certificates that you then import back into IIS and the server.

3.

Application for a certificate

Here, I will be a wildcard certificate, for example, describes how to create a certificate:

1) Open IIS Manager

2) Click the server name in the main screen double click Server Certificates

3) In the right panel, click Create Certificate Request…

image

4) fill in the following diagram each column, click Next

image

5) Cryptographic Service Provider Properties page to keep the default, click Next.

6) In the File Name page, enter C: \ req.txt , and then click Finish.

7) Run cmd , run

certreq-submit -attrib "CertificateTemplate: WebServer" C: \ req.txt

8) Select the CA , click OK.

9) the certificate is stored as C: \ Wildcard.cer . ( 7-9 can also be in the CA to complete)

10) back to the IIS Manager, click No. 3)  Step graph Complete Certificate Request …

11) Select the C: \ Wildcard.cer , Friendly name named *. contoso.com , of course, you can take a different name.

12) Click OK.

13) so that we completed the wildcard certificate request.

Additional SSL Certificate Imports

1) RUN MMC at the start / search

2) Select File / Add Remove Snapin – Select Certificates – ADD

image

Computer Account

image NEXT / Finish

3) Expand the first two folders, and Right Click on the Certificates Folder and select: All Tasks /  Import.

4) Browse to your wildcard SSL certificate file, and import that into the Personal and Trusted Root Certification Authorities.

image

Ensure that you

Binding site for the default SSL certificate

1) Open IIS Manager.

2) In the Connections panel, expand Sites , click Default Web Site.

3) In the Actions pane, click Bindings.

image

4) In the Site Bindings dialog box, click Add.

5) Type select HTTPS.

6) SSL Certificate , select the certificate you just created *. contoso.com , and then click OK.

image Ours is interactivewebs.com

7) Click Close.

8) Repeat for the Personal certificate folder.

For the CRM 2011 binding site SSL certificate

1) Open IIS Manager.

2) In the Connections panel, expand Sites , click CRM Web Site.

3) In the Actions pane, click Bindings.

4) In the Site Bindings dialog box, click Add.

5) Type select HTTPS.

6) SSL Certificate , select the certificate you just created *. contoso.com .

7) Port to select a different 443 (e.g. 444 ) and port number, and then click OK

8) Click Close.

DNS configuration

For MS CRM 2011 configuration Claims-based authentication, you need the DNS to add some records to make CRM 2011 for each breakpoint can be resolved correctly.

There are two ways you can achieve the desired result. But first lets understand the desired result.

  1. We make the assumption that your server is running at least one static IP address.
  2. Because this is Internet Facing, that IP needs to be accessible to the world.
  3. That same IP can be used for access to your server both internally on the matching we are playing with, and externally form anyone on the net.
Lets Get Basic

Start a Command Prompt, and work out what your IP address of the server is.

Click START > RUN > CMD

Type IPCONFIG – Enter

Under the name: IPv4 Address is a number that looks like: 66.34.204.220

image

That is Your IP Address of the Server.

The DNS Goal

Make sure that when you PING xxx.domain.com that it points to that IP address. Both for the world and for you when you do that on your server.

(xxx is the sub domain that we are about to configure.)

To configure CRM, we need some sub domains to point to the server IP.

  1. sts.domain.com
  2. auth.domain.com
  3. dev.domain.com
  4. Your ORG name.  org.domain.com (Where ORG is the CRM deployment name of your organization or organizations), e.g.

image

We have two setup here: CRM and CRM2011. So we need to configure crm.interactivewebs.com and crm2011.interactivewebs.com.

Hosting Your Own DNS

If you host your own Domain Name Server (DNS) and you host the domain name that you are using to setup IFD. Then configuring an A record for the above mentioned sub domains is easy.

START > Administrative Tools > DNS

Find your Domain Name

Right Click and select NEW HOST A

image

image

Add an A record that points to your servers IP address.

Repeat this process for all of the above mentioned sub domains. auth, sts1, dev, and your own organization names.

Test DNS

You must be able to ping all of those names and get the correct server IP address. Both from computers on the internet, and from the server.

Note: If you have added the DNS records, but still encounter name resolution problems, you can try running on the client ipconfig / flushdns to clean up the cache. You can also click the DNS server root and click CLEAR CACHE so that the server is responding with the latest updates.

image

Note: Don’t bother proceeding past this step if you cannot ping your sub domains internally and externally correctly.

Firewall configuration

You need to set the firewall to allow the CRM 2011 and the AD FS 2.0 port used by the incoming data stream. HTTPS (SSL) is the default port 443.

For Initial setup testing etc. We recommend just turning the thing off. Better start from a place where it does not muck you around, then turn it all back on after you are successful.

image

Configuration Claim-based authentication -internal access

Configure the internal access Claim-based authentication requires the following steps:

  • Install and configure AD FS 2.0 .
  • Set Claims-based authentication configuration CRM 2011 server.
  • Set the Claims-based authentication configuration AD FS 2.0 server.
  • Test claims-based authentication within the access.
Install and configure AD FS 2.0

CRM 2011 with a variety of STS provider ( STS Provider ) together. This article uses Active Directory Federation Services (AD FS) 2.0 to provide a security token service (security token service ).

Note: AD FS 2.0 will be installed to the default site, so install AD FS 2.0 , you must have CRM 2011 installation in the new site. (Remember we said that earlier)

IIS Looks like this if it is correctly installed: image

If you only see the default website with CRM installed in that. Start AGAIN!

Download the AD FS 2.0

From the following link to download the AD FS 2.0

Active Directory Federation Services 2.0 RTW( http://go.microsoft.com/fwlink/?LinkID=204237 ).

Install AD FS 2.0

In the installation wizard, select the federation server role installed, for more information refer to

Install the AD FS 2.0 Software( http://go.microsoft.com/fwlink/?LinkId=192792 ).

Configure AD FS 2.0

1 in the AD FS 2.0 server, click Start , then click AD FS 2.0 Management .

2 In the AD FS 2.0 Management page , click AD FS 2.0 Federation Server Configuration Wizard .

image

3 In the Welcome page , select Create a new Federation Service , and then click Next.

image

4 In the Select Deployment Type page , select Stand-alone Federation Server , and then click Next.

image

5 Choose your SSL certificate (the choice of a certificate created *. contoso.com ) ,add a Federation Service name ( for example , sts1.contoso.com), and then click Next.

image

Note: Only you as the AD FS 2.0 sites when using the wildcard certificate, only need to add the Federation Service name.

6 Summary page, click Next.

image

7 Click Close to close the AD FS 2.0 Configuration Wizard.

image

Note: If you have not added ( sts1.contoso.com ) to add DNS records, then do it now.

Verify the AD FS 2.0 is working

Follow the steps below to verify that the AD FS 2.0 is working :

1 Open Internet Explorer.

2 Enter the federation metadata of the URL , for example:

https://sts1.contoso.com/federationmetadata/2007-06/federationmetadata.xml

3. to ensure that no certificate associated with the warning appears.

image