SQL Script to find instance name

DECLARE @GetInstances TABLE 

( Value nvarchar(100),  

  InstanceNames nvarchar(100),  

  Data nvarchar(100))  

Insert into @GetInstances 

EXECUTE xp_regread   

    @rootkey = 'HKEY_LOCAL_MACHINE',   

    @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',   

    @value_name = 'InstalledInstances'  

Select InstanceNames from @GetInstances

Installing and Configuring Remote Desktop Services (Terminal Services) on Windows Server 2008 R2
>> RDS Service
>> Remote desktop web access 
>>Publishing application

In the latest release of Windows 2008 R2, Terminal Services has been renamed Remote Desktop Services (RDS).  There are many enhancements in this release including enhanced multimedia performance and the ability to publish custom applications sets to specific users through Remote Desktop Web Access.  The Web Access role as the name suggests allows users to access Remote Desktop applications through a web site.  This feature is official known as RemoteApp.  The new name for the core Terminal Server running in application mode for user sessions is Remote Desktop Session Host.

In my environment I will configure the Remote Desktop Session Host and Web Access roles on the same server.  I will also install the Remote Desktop Licensing role on a dedicated server so that any additional Session Hosts I add in the future can share this service.  The License Server role can be installed on your Session Host server if desired.  All of my servers are members of an Active Directory domain.

Install Remote Desktop Licensing Role

To start go into the Server Manager on the server that will host the Licensing role service.

Under Roles Summary select “Add Roles”.

Click Next.

Check Remote Desktop Services and click Next.

Click Next.

Check Remote Desktop Licensing and select Next.

Discovery scopes are no longer used for licensing with Windows 2008 R2 Remote Desktop, we can assign these to our Remote Desktop Session Host manually or through a group policy.  There is a 120 day grace period allowed for connections to a Remote Desktop Session Host before a license server needs to be activated.  Click Next.

Confirm the settings and click Install.  Then click Close once the install is completed.

Install Remote Desktop Session Host and Web Access Roles

Now switch to the server that will be our Remote Desktop Session Host and Web Access server.  Start Server Manager.

Select “Add Roles”.

Check Remote Desktop Services and click Next.

Check the RD Session Host and RD Web Access services.  When you add Web Access you’ll be prompted:

Click Add Required Role Services to accept to install the prerequisites.  Then click Next back at the Add Roles Wizard.

At this time I’ll select “Do not required Network Level Authentication”.  There are some issues using NLA if we have Windows XP SP3 clients, but there is a registry fix available.  Click Next.

I’ll choose Per User licensing mode and click Next.

Now we can add additional groups of users to have access to applications on the Session Host.  The local Administrator group on the server is included by default.  I’ll add additional groups later so for now I’ll click Next.

I’ll check all of the available check boxes to provide for an enhanced client experience on this Session Host.  This will allow for capabilities such as playing multimedia through the RD session and Windows Aero if we publish a full desktop on this server.  This all sound great, but be aware that these features will consume additional resources on your Session Host.  Click Next.

Click Next, then click Install at the confirmation screen that follows.  The server should prompt for a reboot when the installation is complete.

Configure Session Host to Use Licensing Server

After you reboot and log on to your Session Host and go to Start > Administrative Tools > Remote Desktop Services > Remote Desktop Session Host Configuration.

In the middle pane under Licensing double click “Remote Desktop license servers”.

Click Add.

Your license server should appear under known license servers.  If not, enter the FQDN or IP address of the server and click Add.  If you are not logged on as a Domain Administrator and the License server is on a different server in your domain it will give you a warning about being unable to verify, it should be okay to simply click through this message.  Click OK.

Add Users for Remote Desktop on the Session Host

Now we’ll go into Computer Management and add the additional users group(s) that we want to allow access to Remote Desktop sessions on the Session Host.  Go to Start > Administrative Tools > Computer Management.

Open up “Local Users and Groups” on the left pane, then highlight the Groups sub-folder.  Double click the “Remote Desktop Users” group in the center.

I’ll add the Domain Users group.  Click Add.

I will allow all of my domain users access to Remote Desktop sessions, so I’ll type Domain Users, click Check Names, then click OK.  Click OK through the remaining dialog windows, and close Computer Management.

Testing RemoteApp
  

Publish a Remote Desktop RemoteApp Program

Now it’s time to publish a test application.  Go to Start > Administrative Tools > Remote Desktop Services > RemoteApp Manager.  Click “Add RemoteApp Programs” in the right Actions pane.

Click Next.

I’ll test using the Calculator, so check that and click Next.  At the confirmation window click Finish.

Test Newly Published RemoteApp Program

Now I’ll log on to a client machine in my domain to test the newly published Calculator.  On the client open up Internet Explorer and enter “http://YourWebAccessServer/rdweb”.

Click “Continue to this website” in the browser window.

You may receive a warning about the ActiveX control not being installed.  I’ve generally only seen this on Windows XP clients.  To fix this reset your IE internet settings by clicking Tools > Internet Options.

Click the Advanced tab.  Then click the Reset button.  Click through the dialog boxes.  Once you’re back at the IE browser windows, close IE and reopen it.  Now you go back to “http://YourWebAccessServer/rdweb” and click through the certificate warning you should be presented with the RD Web Access log on screen.

When a user with standard privileges on the client accesses the RD Web Access site for the first time they will receive a warning about installing the Remote Desktop ActiveX Control.  Confirm that you trust the web site address you are contacting.  You probably will because you should be accessing your own server!

Click the warning message at the top and select Run Add On.

Now click Run.

Log on with a user you’ve given access to Remote Desktop sessions.  The administrator should automatically be given access.  Be sure and specify the domain first if you are using a domain account.

You’ll receive a warning about unable to identify the remote connection publisher, click Connect.

It will now ask for you to log on to the the RD session.  Enter the user name in the Domain\Username format.

And another warning will appear about identity verification, click Yes.  We’ll set up a new certificate from a trusted certificate authority later to eliminate this message.  The RemoteApp should now launch.

Eliminate Certificate Warnings (optional)

Now let’s work to eliminate the multiple certificate warnings we receive when accessing our Remote Desktops and RemoteApp programs.  In this example I will make use of an enterprise certificate authority configured in my AD domain, of which my RD Session Host/Web Access server is a member.

Note that this process will only eliminate the warnings if my clients and RDS server are members of the AD domain since they will automatically trust the AD enterprise certificate authority.  If you have non-domain clients that connect to your RDS server you’ll need to export and import your CA root certificate into the certificate store on these systems so the IIS and RDS certificates are trusted.  

Eliminate RD Web Access/IIS Certificate Warning

First if you have not done so in your environment already, Install an Enterprise Certificate Authority in Windows 2008 R2.

Next, Configure a Server Certificate for IIS/RD Web Access.

These two steps should take care of the warning we receive when we access the RD Web Access web site.

Eliminate RemoteApp Certificate Warning

Now let’s remove the first RemoteApp certificate warning.  On the Session Host navigate to Start > Administrative Tools > Remote Desktop Services > RemoteApp Manager.

Under Overview click Change after the Digital Signature Settings heading.

Under the Digital Signature tab check the box for “Sign with a digital certificate”.  Then click the Change button.

Select the certificate published from our enterprise CA.  I’ll use the certificate I requested for my IIS/RD Web Access service.  Be sure and choose the certificate issued by the CA and not the self signed certificate.  Click OK.

Click OK back at the RemoteApp Deployment Settings box.

Once a certificate from a trusted certificate authority is configured, the RemoteApp session should pass through the authentication credentials entered on the RD Web Access page and the second log on to the Session Host will no longer be necessary.  Yes!

Eliminate Remote Desktop Connection Certificate Warning

I have noticed that this step is not needed with Windows 7 domain member clients, but for good measure I wanted to make sure that all of my potential clients are cleared of certificate warning messages.  On the Session Host go to Start > Admin Tools > Remote Desktop Services > Remote Desktop Session Host Configuration.

In the center pane under Connections double click the RDP-Tcp connection.

In the General tab under Certificate click the Select button.

Again I’ll use the certificate I published for the IIS/Web Access role.  Click OK.

Back at the RDP-Tcp Properties box click OK.  We should now be configured to access our RemoteApps without any more annoying certificate warnings!

How to use DTCPing and DTCTester? Difference between “DTCTester and DTCPing”. “How DTCPing and DTCTester work”

 The basic difference between DTCPing and DTCTester is as follows:

1. DTCPing need to be started on both the machines for testing communication between them.

2. You don’t need to give password in DTCPing whereas you type password on DTCTester. The password is visible to others on DTCTester so be careful.

3. You need to configure ODBC connection while using DTCTester whereas there is no need to configure ODBC connection while using DTCTester.

4) DTC Ping tests connectivity issues due to firewall and RPC, where as DTCTester can even connect to the DTC and commit transactions to test its working. So DTCTester can also find configuration and Authentication problems

DTCPing Download Link: http://download.microsoft.com/download/b/8/8/b8841bfc-8bd3-4fea-a5f5-06e1f162bd9a/dtctest.exe

DTCTester Download Link: http://www.microsoft.com/en-us/download/details.aspx?id=2868 

How to run DTCPing:

1. Login to m\c A and run DTCPing.exe.

2. Login to m\c B and run DTCPing.exe.

Screen Shot:1

3. On m\c A, type the NetBIOS name of m\c B, and then click Ping.

4. On m\c B, type the NetBIOS name of m\c A, and then click Ping.

5. The log is created and the file name is displayed on the DTCPing screen as follows:

The DTCPing performs following functions:

1. Tests name resolution.

2. Tests Remote Procedure Call (RPC) communication.

3. Detects and display all the registry key settings touched by MS DTC.

4. DTCPing Tests DTC communication between the hosts.

5. Logs the connection communication in the log file.

How to use DTCTester:

This is explained as follows using screenshots. To use DTCTester you need to configure ODBC and then run a simple command. Go through the screenshots step by step.

1.         Screen Shot:1

2.        User 'User DSN' click Add and then select SQL Server- Finish.

Screen Shot:2

3.        Type in a Name for the DSN, Tuition for Example.. a description

             Now, most importent: Type in your SQL Server name / Instance name

In the scrren shot below, TuitionSQL is my SQL server name and SQL2008 is the instance name. 
Click here for SQL Script that will display Instance name.

Screen Shot:3

4.       Use Below mentioned settings if you are using Windows Authentication in SQL, else you can set it to SQL authentication.

        Screen Shot:4

5.        Screen Shot:5

6.         Screen Shot:6

7.        Once the ODBC connection is configured and tested, you can use DTCTester tool for testing the connectivity to MSDTC.

Command: dtctester <dsn name><user name><password>

If you are extracting DTCTester tool to C:\Dtc folder, and using SQL authentication with SA with password Welcome@123 , you need to run the following command

C:\Documents and Settings>c:

C:\Documents and Settings>cd\

C:\>cd Dtc

C:\Dtc> dtctester Tution sa Welcome@123

Screen Shot:7

Issue with Incoming Caller Authentication for MS DTC on Windows 2008 and Windows 2008 R2 Cluster-

Errors: Issues like,

a) Local transactions will get committed, but network transactions will be aborted.

b) MSDTC Trace when extracted using TraceFmt.exe (or msdtcvtr.bat) showing

eventid=RECEIVED_ABORT_REQUEST_FROM_BEGINNER  

(How to read MSDTC Trace)

c) DTCPing giving RPC Errors (How to use DTCPing)

d) DTCTester giving errors like (How to use DTCTester)

SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server Driver] Distributed transaction error'
SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid cursor state

SQLSTATE=37000,Native error=8509,msg='[Microsoft][ODBC SQL Server Driver][SQL S
rver]Import of Microsoft Distributed Transaction Coordinator (MS DTC) transacti
n failed: 0x8004d00e(failed to retrieve text for this error. Reason: 15100).'

Fix1: Check Firewall

1) Check if Windows firewall is disabled, if you are using it, makesure that MSDTC is in exception list.

Ref:: http://technet.microsoft.com/en-us/library/cc725913(v=ws.10).aspx

2)  If the cluster node and the remote system accessing it are in different network and seperated with a fireall, then makesure that the MSDTC ports are enabled.
Ref:: http://www.lewisroberts.com/2009/08/16/msdtc-through-a-firewall-to-an-sql-cluster-with-rpc/

Fix2: NetBios names

Check if the remote host is able to ping SQL Cluster NetBios name, Node1 NetBios name and Node2 NetBios name. If not add NetBios name and IP address in C:\system32\drivers\etc\hosts file to enable it.

Sameway try to ping Remote Host NetBios name from Node1 and Node2, if not pinging, then make respective entries in C:\system32\drivers\etc\hosts file.

Fix3: Check if network DTC is enabled and Authentication mehod is set correct.

Open "Component Services" MMC, you can find this MSDTC instance on the left tree. Right click the clustered MSDTC instance and select "Properties", then check the following items.

The MSDTC transaction mode must be set to either No Authentication Required or Incoming Caller Authentication Required to function correctly on a Windows Server cluster. Incoming Caller Authentication Required is the recommended option because this option is more secure.

Fix4: Check SQL Cluster Resources 

1)  Check MSDTC resource and makesure that the dependency is set to Cluster Group Name and MSDTC disk.



2) Open the Properties page for the Name resource which is consifured as a dependency on MSDTC in this case -the name CLUS1Dtc - and check Resource Display Name, here in the below mentioned example, it is MSDTC Network Name (Dev01). Same time the DNS Name is CLUS1Dtc.

3) To use Incoming Caller Authentication Required for MS DTC on a Windows 2008 or R2 cluster, the display name of the network name resource for MS DTC must be same as its DNS name.

4) The following command will change the display name of the server network name resource from MSDTC Network Name (Dev01) to CLUS1Dtc:

Cluster.exe res “MSDTC Network Name (Dev01)” /ren:CLUS1Dtc

5) Check properties again and confim the name change.

6) Restart the cluster group and test MSDTC transactions again....

----------------------------------------

R Hari