.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Enabling Encryption on SQL 2008 R2 Cluster

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server
Hi All,
I have a SQL Server 2008 R2 (10.50.1600) cluster running on Windows Server 2008 R2.  It is a three-node cluster and there are four instances.  We are pre-production and the customer has tasked me with setting up client/server encryption for three of the four instances.  

I have researched lots of different KB articles and blogs (see below), but I'm still having trouble installing the cert.  I know the basic approach is to:
1. Get a cert that matches the FQDN of each clustered SQL instance (e.g. Instance01.domain.local), using a cert that has "Server Authentication" capability.
1a. Certificate has to be requested and installed by the SQL Server service account (e.g. Domain\svcSQL).
2. In the SQL Configuration Manager, go to "SQL Server Network Configuration", right click on "Protocols for Instance01", and click on Properties.  Then click on the Certificates tab, and select the cert I just generated.  
3. Repeat this for all nodes in the cluster, for each instance I am adding a cert to.

But it's been a struggle, and though I finally was able to request and receive a cert, it does not show up for selection in the Protocols dialog.  What have I done wrong?


View Complete Post

More Related Resource Links

SQLServer 2008 Tools and SQL 2005 Cluster

I recently built a SQL Server 2005 cluster on the o/s Windows Server 2008 R2.  I have been attempting to connect to the cluster from my Windows 7 dekstop with my SQL Server 2008 Tool set.  I have been unable to attach to this cluster since its creation. The firewall is temp off, remote connections in the surface area configuration is set to pipes and tcp/ip and the Browser surface is up and running. I ran Wireshark against the cluster and I could see that there was UDP conversations for ms-sql-m (SQL Port) but that was as far as it would get.  I am using a named instance so not using 1433, but I do see the cluster offering up high RPC ports but the client never responds after the initial UDP response. When I noticed that there was a two way conversation taking place, I went to a seperate machine and fired up SQL Server 2005 Tools and I was easily able to attach to the cluster.  So my question is - Are there any known incompatibilities between SQL Server 2005 cluster services and SQL Server 2008 Tool set?  I can use my 2008 tools to attach to a non-clustered SQL Server 2005 w/o issue.   Error when attempting to connect: A network-related or instance-specific error occured while establishing a connection to sql server.  The server was not found or was not accessible.  Verify that the instance name is correct and the SQL Server is con

Windows 2008 R2/SQL 2008 SP1 CU5 active/active cluster keeps failing

I have set up many of the same clusters in the last few months, but this cluster specifically has been having lots of problems lately.  OS: Windows Server 2008 R2 latest patches SQL: SQL Server 2008 SP1 CU5 (version: 10.0.2746) Disks: VMAX SAN In the very beginning when I set up the cluster, the cluster validation kept failing when I tried to add a node.  Turned out that we had to unjoin the servers from the domain and rejoin.  Right after that the cluster validation succeeded, the second node joined the cluster, and quorum was changed to "Node and Disk Majority".  SQL was installed and set up as active/active (one sql instance on each node).  No issues then until SQL server was actually in use.  Symptoms: SQL server has heavy ETL processing plus being a subscriber of a sql replication.  The following errors first started on 4/24 when I set up sql replication (these servers are replication subscribers): EventID: 1127, Source: FailoverCluster, Task Category: Network Manager Cluster network interface 'man1fscl01a - Hartbeat to man1fscl91b' for cluster node 'man1fscl01a' on network 'ClusterHeartBeat' failed. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapter. Also check for failures in any other network comp

SQL 2008 Restore on a 2008 R2 Cluster

I've built a 2008 R2 Cluster running sql 2008 as a clustered application.  We do full backups with arcserve.  I'm trying to come up with a process to get the cluster back online for disaster recovery.  I can get the Windows cluster back online but when it comes to sql I get stuck.  Would I basically get the cluster back online than use the command - setup /action=rebuilddatabase /instancename=MSSQLServer /sqlsysadminaccounts=domain\username?  Than start the database in single user mode, install the arcserve sql agent and restore all the db's? When I try this I get the error element does not exist error.  Right, now I'm starting from scratch to see if I get the same result.  I just want to make sure I'm on the right track and not spinning my wheels trying something that won't work in a clustered application.   Thanks,   Matt

SQL Server 2008 R2 installation on a server which is in cluster

Hello, I want to install SQL Server 2008 R2 on a machine which is in a cluster. Is there any issue in doing this? Regards

Enabling SA account on SQL Server 2008 R2

Hi I'm setting up a SharePoint 2010 development machine and hit a snag. I installed SQL Server 2008 R2 with the default instance (devserv1\mssqlserver). That's configured for mixed mode authent and I can login using either Windows Auth or SA no worries. Note that it's just a local machine account and NOT a domain account (this is an isolated VM with no domain) I installed SharePoint 2010 on the machine. I don't recall changing any settings and during the install, it has created a new instance called devserv1\sharepoint. I can login to it using Windows Authentication, but I don't appear to have any permissions. Also, the SA user is disabled, but I can't enable it, because I get a permissions error when using my windows account to do it... One thing I noticed that is a bit weird is the version numbers of the 2 instances - the first/default instance is 10.5.0.* whereas the one created by SharePoint install is or thereabouts - i.e., it's SQL 2008 but not R2 which I don't reallty understand. Anyway, how can I enable the sa account on the sharepoint instance? Thanks.weirdbeardmt, PhD, BSc. Silly name, but lots of letters.

SQL Server 2008 Agent Fails to start in a Win 2008 Cluster

When I try to bring SQLAGENT Online, I get the following errors: EventID:53 [sqagtres] StartResourceService: Failed to start SQLSERVERAGENT service.  CurrentState: 1 [sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 435) [sqagtres] OnlineThread: Error 435 bringing resource online.   I just did a fresh SQL Cluster install as well.  When I installed the cluster, the service account was good to go.  I cant figure out what is going on.  SQL Server Engine starts fine.  Any ideas?

SQL 2008 64 Bit 2 node cluster

I wanted to get some feedback on how much memory and CPUs I needed for each node in a 2 Node Cluster. The operating System will be Windows 2008 R2 64 bit Enterprise and the SQL version will be SQL 2008 R2 64 Bit. It will have a total of 8 instances of SQL Server. 2 of those instances will be SQL Server Reporting Services ( 1 SQL Native and the other SharePoint enabled). This cluster will have both nodes in a virtual enviroment. Any feedback would be greatly appreciated.

named instance sql server 2008 install on cluster

I have a production two-node cluster with sql server 2008 sp1 cu7 running over windows server 2008 sp2. If I were to install a new named instance on this live environment, would it impair the default sql server instance functionality? I'm not referring to processor/io while the install is in progress. My question specifically is would the install process by its nature bring down the default instance. I'm pretty sure it won't, but don't see any documentation that would confirm this. thanks.


Platform:   2 servers (clustered) with windows server 2008 x64 r2 (enterprise) Application: sql2008 x64 enterprise with SP1 (slip streamed) Problem: sql2008 new cluster install fails using the COMMAND LINE with a CONFIGURATIONFILE.INI The install starts but stops with the log file in the setup bootstrap folder  => (Detail_ComponentUpdate.txt) below. Bottom lines indicate a REBOOT is required; did a reboot on both nodes and still have the same problem. Used the following command line to launch sql2008 setup: Setup.exe /Q /SQLSVCPASSWORD="**********" /AGTSVCPASSWORD="**********" /ASSVCPASSWORD="**********" /ISSVCPASSWORD="**********" /RSSVCPASSWORD="**********" /ConfigurationFile=ConfigurationFile.ini Anyone has any success installing sql2008 using command line and command file? thanks john ------------------------------- tail end of the Detail_ComponentUpdate.txt file ----------------------------------------------- 2010-09-01 15:59:44 Slp: ------------------------------------------------------------ 2010-09-01 15:59:44 Slp: C:\SQL2008 Enterprise with SP1\x64\FixSqlRegistryKey_x64.exe exit code: 0 2010-09-01 15:59:44 Slp: Launching external tool: C:\SQL2008 Enterprise with SP1\x64\FixSqlRegistryKey_x86.exe 2010-09-01 15:59:44 Slp: ------------------------------------------------------------ 2010-09-01 15:59

SSRS 2008 SP1 - Enabling "My Reports" allows users delete reports in folders they should not be able

Issue: Users that have “My Reports” SSRS Role assigned to them can delete reports in other “shared” folders. My understanding was that they will be able to do the tasks specified only  in the “My Reports” workspace not everywhere. I have “My Reports” enabled via SSMS. Users have “My Reports” role assigned to them at the root level. My SSRS Role “My Reports” has only the following tasks assigned: 1.       View Reports 2.       Manage reports 3.       View Folders 4.       Manage Folders To test, I'm using Development version of SQL Server 2008 SP1. Thanks in advance, Martina

SSRS 2008 on cluster suggested/supported?

Hello all, I am a windows admin with intermediate knowledge of SQL Server and its components like SSRS. Anyway, I built a production SQL2008 cluster successfully. The head developer also wanted to install SSRS2008 on one of the nodes along with a custom .NET framework application. Needless to say I did get SSRS2008 installed on the active node, but starting having problems with the cluster services and other strange happenings. So I removed SSRS2008 from the node and created a VM, installed SSRS2008 ENT(gonna have to uninstall/reinstall Standard) on that and created the databases via the config wizard and this vm is also running the custom .NET framework application. All of the DBAs agreed that this should have not been installed on the cluster. Now I have to justify why I did this...... 1. For a Production cluster environment isn't is suggested to have ssrs installed on a separate server with it's database housed on the cluster? ( I was told this was an "old school" way of thinking by the developer). 2. Is having SSRS installed on one node of the cluster supported by Microsoft?  In my humble opinion, a database cluster is meant for housing databases only for high availability and nothing else should be present on these two nodes.  Is there any documentation for SSRS that suggests the seperate server deployment rather than including t

Upgrade SQL Server 2000 to SQL Server 2008 in Cluster--Recomendations

Hi all I have   6 cluster environments and 10 standalone servers and mix  of  32 bit  64 bit. As I know In place Upgrade not possible SQL2k 32 bit to SQL2008 64 bit. What I want to know is……… 1. If I purchase SQL Server 2008 Ent edition that will have both 64 bit and 32 bit edition or I have purchase separately ? 2. How to do side by side upgrade in Cluster environment I mean I can’t ask my company to provide other cluster to do side by side upgrade. If I do installation in same cluster machines, I can’t provide same Instance name for SQL Server 2008 Instance.hance it is very difficulty for application connectivity Please some body provides me best approach to drive in Upgrade from SQL2k to SQL2008. Thanks in Advance      SNIVAS

SQL server 2008 failover cluster install

Hello all, Looking for the definitive answer with supporting documentation (link). Does a SQL server 2008 cluster have to be installed as or run as a domain service account or can local accounts be used?  I looked for a document that stated yes or no and could not find one. If anyone can provide info it would be much appreciated. Have all ready looked at these 2 links of interest. http://msdn.microsoft.com/en-us/library/ms143504.aspx#Service_SID http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx Thanks in advance,  

sql server 2008 cluster multi instance

we have a cluster with 2 nodes and we are thinking to add another instance so that node 1 takes care of instance 1 and node 2 takes care of instance 2, and at anytime they are watching each other's back. How is this done???? what is the procedure? thanks.  

in-place upgrade in cluster environment(2000 to 2008),How to do compatabilty Test ???

Hi all As of I know there are three tools are involved in Upgrade process. 1.UpgradeAdvisor 2.RML utility 3.SQL Server Upgrade Assistant. I want do compabilty test...which tools in Best for that...  Thanks in advanceSNIVAS

SQL Server 2008 side -by-side installation in Cluster w2k3 sp2.

Hi  I am ready with In-Place and went to Sr.DBA Manager, He rejected and he want 100% Database guarantee and 100% safe, down time does not matter for him and recommended me go a head with Side by Side. 1.If detach Database from SQL2k and attach to SQL2k8,I don't think  it is possible to attach back...is it correct 2. Is it required Create new cluster group for SQL2k8? 3. In 2 case what are the cluster groups I have to Create 4. Is it required to change connecting string from application? 5. If it is where i have to modify the connecting string in application level or in Database server level? 6. What are the settings can I take care after installing new sql server instance?  7. Is it required new virtual instance name for new installation (Instance)? 8.Is there any way to use existing virtual instance name for New instance?(so I belive no Need to change connecting string in application) 10.Is it possible to change instance name?(Old instance name to some other name and new instance name as existing instance name).  SNIVAS

SQL 2008 Active - Acvite Cluster

I have 2 node windows 2008 cluster server, I installed sql 2008 active - active cluster this is work fine but, when i move one instance to other clusrter server, instance has failed ( SQL Server and SQL Server Agent resusorce) and i received this event log on the server what can i do ?Thank You "The description for Event ID 19019 from source MSSQL$INSTANCE2 cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer."  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend