SQL 2012 and older versions of Sitecore

So I just wanted to share something.  I have a client who is using 6.2 and SQL 2005.   They want to upgrade to 6.5.  They have SQL 2005 and SQL 2012.  Sitecore officially says that SQL 2012 is not supported for versions less than 6.5.  The option of moving to SQL 2008 , performing the upgrade, and then moving to SQL 2012 was less than fun to consider.  Instead, I tried doing a Sitecore 6.2 install, attaching the 6.2 databases to my SQL 2012 server.  Worked like a charm.  I’d say that for a short term “need to upgrade and don’t want to waste time with 2008” scenario  you can go ahead and try attaching your databases to SQL 2012 and doing the upgrade.  I have yet to do the actual upgrade, but I suspect that it should go just fine.

 

Sitecore and Trusted Connections and AD

The Requirements

So I recently did an install with a client. They had some pretty specific needs when it comes to the security on their sites and I thought I would share a few of the things that I learned along the way.  In fact, much of this appeared to work one way in previous versions of Windows and no longer works the same.

The first thing is to mention that although some of this is already documented in Alex Shyba’s blog post ,  there were gotchas that I didn’t realize and this actually addresses a situation in which your CD server doesn’t have access to the domain.  I thought I’d share this step by step for the people like me who are developers but aren’t network admins and want a straightforward guide.

First thing is to spell out is our specifications.

  • Need to set up both a CM and a CD server on different servers
  • CM server located in the domain and needs to use AD for Sitecore back-end access
  • CD server is going to reside in the DMZ outside the domain with NO POSSIBILITY of setting up a trust
  • We will have a couple of different web databases (one for a preview version of the site), but this is completely optional to configure
  • SQL server is the same for both servers and resides inside the domain network and will be accessible from DMZ on a non-traditional specific port only
  • Security is VERY tight and there is absolutely no possibility of having any connection strings in plain text anywhere in the solution code

Now, as part of the prep, a domain user was created for the purposes of the Sitecore sites.  Our approach was to set things up on the database first.   You can use the Sitecore installer for DB only, but the only access I had was via SQL Management studio remotely logged in with an SA account with anything I needed done directly on the server being done via my verbal instructions.  We opted to just do the install manually, so there may be some back-end sysadmin stuff that was done that I am not aware of, but I’ll try to outline this stuff, step by step.  Here’s what we did.

Set up Database Users

Since there are 2 different instances that will be accessing the database, 1 using Windows Authentication and 1 using SQL Authentication, I configured both users at the same time and with the same permission set (almost).  So the first thing is that the domain user (ad\CMUser) was added as a windows user with limited access to the box.  At the same time, a local windows login was created as well with limited rights, including that they could not change their password and that they didn’t have remote log in rights to the server.  We added both users as SQL logins and mapped out the DB privileges:

CMUser and CDUser –  Core  :   db_datareader, db_datawriter, public, aspnet_Membership_BasicAccess, aspnet_Membership_FullAccess, aspnet_Membership_ReportingAccess, aspnet_Profile_BasicAccess, aspnet_Profile_FullAccess, aspnet_Profile_ReportingAccess, aspnet_Roles_BasicAccess, aspnet_Roles_FullAccess,aspnet_Roles_ReportingAccess

CMUser only – Master  :   db_datareader, db_datawriter, public

CMUser only – Web (Preview) :  db_datareader, db_datawriter, public

CMUser and CDUser – Web (Production) : db_datareader, db_datawriter, public

CMUser and CDUser – Analytics  : db_datareader, db_datawriter, public

We also had to edit each of the databases to provide our users with Execute rights for the databases.

Configuring the Content Management Webserver

The first thing is that you’re going to need to set the ad user with the right privileges on the web server.  The first thing I did was to add this user to the IIS_WPG group.  Now this user group may vary depending on the version of windows and IIS that you’re using, so you may need to check the naming on this.

Next thing was to actually install Sitecore.  This is pretty straight forward and we actually used the installer for this.   Don’t worry too much about the database part of the installer since we’re going to change all of that anyway. I know that it appears to need a valid login, so you can put in the SA or whatever login you have to get passed this part and change it later.  Since this is a Sitecore 6.5 site, we went with setting the app pool to run in Integrated mode using .Net 4.0.

Once the install is done, we’ll need to configure things.  Now, there are 3 parts to why we’re using the AD user for everything.

  1. The first thing is that we want to run the AppPool as a specific user, which places all the security in the hands of IT, rather than having that information accessible to developers.
  2. In order to use a trusted connection when accessing the databases, it uses the app pool identity and windows authentication.
  3. Regardless of database connections, if using AD, you still need to access the LDAP and if the app pool user is an AD user, you don’t need to put user/pass in the provider declaration.

So, to start, in order to give a custom user the proper levels of access to run as the app pool user, we used the aspnet_regiis.exe tool to set the user as a system account.  This is accomplished by running the aspnet_regiis.exe command with the -ga switch and the fully qualified domain name like so:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -ga ad\CMUser

You should see this where the blurry part is the domain\user name:

To change the app pool user, looking at the properties for the App Pool, click the Advanced Settings and click the button by next to the identity.  You would select the option for Custom and then put in the domain\user along with the  password.  Once you do that, this site will be running in the context of your AD user and since our AD user also has access to the SQL box and databases, we can change the connection strings as well.  They should look something like this given the above server configuration:

<add name="analytics" connectionString="Trusted_Connection=Yes;Data Source=MyDatabase;Database=Sitecore_analytics"/>
<add name="core" connectionString="Trusted_Connection=Yes;Data Source=MyDatabase;Database=Sitecore_Core"/>
<add name="master" connectionString="Trusted_Connection=Yes;Data Source=MyDatabase;Database=Sitecore_Master"/>
<add name="web" connectionString="Trusted_Connection=Yes;Data Source=MyDatabase;Database=Sitecore_Web"/>
<add name="web" connectionString="Trusted_Connection=Yes;Data Source=MyDatabase;Database=Sitecore_Web_Prod"/>

Configuring the Active Directory Module

If you have set things up right, you should now be able to browse the website and log in with the admin user.  Now, let’s get things set up to use the AD module.  The first part is to install the AD package and there’s nothing out of the ordinary about that.  That simply puts the files and items in place and configuring things is will be the next step and is actually very easy when the app pool user is an AD user.  Most of this stuff is covered very well in the AD documentation, but I’ll just touch on some things that aren’t covered and maybe just the steps I took.

The hardest part, I thought, is getting the right connection string.  It’s very helpful to download and install an LDAP browser.  I recommend Softerra.  They have a light weight, free version that actually has live LDAP servers pre-installed for you to browse and get a feel for what you’re looking for.  If you’re on a domain, it also has the ability of looking up what domain you’re on and helping you connect to it so you can see what your own connection string should be.  It’s also very good at helping to create your connection string because it actually maps it out for you and is especially helpful if you want to use custom filters, which is great when your users are in one area and groups are in another and you don’t want to have to pull in the whole directory.

Getting your connection string is as simple as creating a new Profile.  This brings up a wizard.  If you click the ‘Lookup Servers…’  button, it’ll go find the servers that are available to you… which, if you’re logged in to the domain, finds your domain controllers.

Lookup Servers

You might have several to choose from, so you may need to check with a sysadmin, but in my personal experiences, any of them worked.  Once you choose one, you will have to set the base DN.   Click the “Fetch Base DN’s” and it’ll create some strings for you to choose from.  I’d suggest that if you’re not sure which to use, just go with using the one that looks something like your domain with a  DC= before each part of your domain name.  For example,   DC=mydomain,DC=com if your domain is mydomain.com.  After selecting your credentials (you can start off with just choosing the option for the currently logged in user), you can now browse your LDAP and work out the exact connection string.  Now, as I mentioned above, the AD module uses groups and users and if they’re in 2 different areas, just find the highest common denominator for your connections. We can user Custom Filters for filtering the proper groups and users; this is only for a connection to the LDAP.  You can then, if you’re using Softerra, simply right click on the folder for where you want to get the connection string from and choose properties.  At the bottom, you’ll see URL and you can copy that whole part as the connection string.  The key things to remember are that you need to start the connection string with ldap:// , you should also use a fully qualified domain name and port followed by a slash and the rest will be the full path to a location where the users AND groups are at.  It should look something like this where the OU entries are before the DC and are in order that go up the hierarchy.  You would then need to add a connection string for the LDAP server in your connection strings file like this (name is whatever you want to name it and can be your domain or another name of your choice):

<add name="mydomainConnectionName" connectionString="ldap://domaincontroller.mydomain.com:389/OU=MyRegion,OU=Locations,DC=mydomain,DC=com" />

More information on this is available in the AD documentation, but the other relevant bits are the other parts I want to touch on are the membership providers.  Since we’re using the AD user, we can avoid the need to use the user/pass and simply place our declarations in the web.config.  Now there are 2 parts to this and the first is the system.web area starting in the membership area around line 3300 (in a more or less clean config file) and should look something like this.  Notice that my connectionStringName is the name I gave it in the connectionString name declaration.  The provider name does not match and does not have to.  Notice also that in my provider declaration for the Sitecore item, the realProviderName has been changed to be “switcher” rather than SQL.  Notice also that I’m not putting in the full string as defined in the AD documentation as some of that refer to options for resetting passwords and such and I choose to put only the essentials:

<membership defaultProvider="sitecore" hashAlgorithmType="SHA1">
 <providers>
  <clear/>
  <add name="sitecore" type="Sitecore.Security.SitecoreMembershipProvider, Sitecore.Kernel" realProviderName="switcher" providerWildcard="%" raiseEvents="true"/>
  <add name="sql" type="System.Web.Security.SqlMembershipProvider" connectionStringName="core" applicationName="sitecore" minRequiredPasswordLength="1" minRequiredNonalphanumericCharacters="0" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="256"/>
  <add name="switcher" type="Sitecore.Security.SwitchingMembershipProvider, Sitecore.Kernel" applicationName="sitecore" mappings="switchingProviders/membership"/>
  <add name="ad" type="LightLDAP.SitecoreADMembershipProvider" connectionStringName="mydomainConnectionName" applicationName="sitecore" connectionProtection="Secure" attributeMapUsername="sAMAccountName" enableSearchMethods="true" />
 </providers>
</membership>
<roleManager defaultProvider="sitecore" enabled="true">
 <providers>
  <clear/>
  <add name="sitecore" type="Sitecore.Security.SitecoreRoleProvider, Sitecore.Kernel" realProviderName="switcher" raiseEvents="true"/>
  <add name="sql" type="System.Web.Security.SqlRoleProvider" connectionStringName="core" applicationName="sitecore"/>
  <add name="switcher" type="Sitecore.Security.SwitchingRoleProvider, Sitecore.Kernel" applicationName="sitecore" mappings="switchingProviders/roleManager"/>
 </providers>
</roleManager>

Don’t forget this part per the AD module documentation:

The connectionProtection attribute set to Secure requires that you add one more element to the <system.web> section. You can place it anywhere inside this section:

<!-- Machine key attributes -->
<machineKey validationKey="BDDFE367CD36AAA81E195761BEFB073839549FF7B8E34E42C0DEA4600851B0065856B211719ADEFC76F3F3A556BC61A5FC8C9F28F958CB1D3BD8EF9518143DB6" decryptionKey="0DAC68D020B8193DF0FCEE1BAF7A07B4B0D40DCD3E5BA90D" validation="SHA1" />

Configuring the Content Delivery Server

This used to be very similar to setting up the content management server.  This is absolutely NOT the case any longer.  To start off, being outside the domain, per a LOT of research, unless you have some way of setting up a trust relationship (which most domain admins are not willing to do), the only option is a local windows user that has access to the SQL box and then you have to obfuscate your connection information.  There may be some tricks out there or this may work a different way in older versions of Windows, but per Microsoft, if using Windows Server 2008 and SQL Server 2008, the only way recommended approach is the following and I couldn’t get anything else to work.

First, we’re going to create a mirrored local account that matches what we used on the SQL box.  This may or may not be necessary, but we did it this way because in connecting to the SQL box at all via TCP/IP connection on a specific port, we couldn’t seem to get this to work until we used our local SQL account.  If anyone knows why this is, please let me know.  Anyway, we made our 2 SQL accounts the same on the CD and SQL box.  Then, as above, we added this user to the IIS group and ran the aspnet_regiis.exe with the -ga switch for the local SQL user to give it system account permissions.

Next thing was to actually install Sitecore and this is the same as the content manager server.   Then we set the app pool to run as this local identity.  The next thing is that because we had to use a specific port (such as 1436 instead of 1433) and we don’t need to access the master db, in the connection strings, we edited our connection strings so that the port is specified with a comma behind the server name.  You can use an IP address instead of a domain name, by the way, but we went with specifying the domain name and adding an entry in the hosts file so that we could easily change the database that it pointed to on the fly without the need to decrypt the connection string.  It looked like this this to start:

<add name="analytics" connectionString="user id=CDUser;password=myPassword;Data Source=myDatabase.myDomain.com, 1436;Database=Sitecore_analytics"/>
<add name="core" connectionString="user id=CDUser;password=myPassword;Data Source=myDatabase.myDomain.com, 1436;Database=Sitecore_Core"/>
<add name="web" connectionString="user id=CDUser;password=myPassword;Data Source=myDatabase.myDomain.com, 1436;Database=Sitecore_Web_Prod"/>

Next, we run a command that encrypts and decrypts the connection string file with the aspnet_regiis.exe tool.  The -pe and -pd switch or used for encrypting and decrypting, but we also add the additional f to signify that the encryption should be on a file and then the path to the ROOT of the website (do not put the path to the file itself.. just the path to the “website” folder is required).  Now, one thing to mention is that anyone who has access to the file system and knows how to decrypt can run this and see your credentials.  I’m sure I don’t have to tell anyone that it’s best to have a method in place to push files without the need to have developers on the box.

Encrypt

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -pef "connectionStrings" C:\inetpub\wwwroot\Sitecore\Website

Decrypt

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -pdf "connectionStrings" C:\inetpub\wwwroot\Sitecore\Website

When it’s encrypted it should look something like this:

<?xml version="1.0" encoding="utf-8"?>
 <connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
  <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element" xmlns="http://www.w3.org/2001/04/xmlenc#">
   <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
   <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
    <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
     <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
     <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
      <KeyName>Rsa Key</KeyName>
     </KeyInfo>
     <CipherData>
      <CipherValue>sDZPMPxsAFllxt35B0vw4a1QqtmO58/HaWIA4BHoPoSY+IYv3XhJ7jy/bgXWy9aOEhUikcWt05m9IJKe3cQu8eqbzVLYIcHx95ZKcKN/RPkY+1Juph3qOcbIAIQQALDDvmHVtoBgeDuB6/ucWK+NiNIqy8bgwNjLPkjI0Skhdn8=</CipherValue>
     </CipherData>
    </EncryptedKey>
   </KeyInfo>
  <CipherData>
   <CipherValue>QBX1tSYhKL3uibyDPCFFKixBxde4btjzu5gR5HdSj+C7riScRA+GmUBsAA/+Ij9Xt13E7Ju7wYpvTilP7akK19Gp3zd4uQX4S1Cbv/nI8yCo7GTjoBLhLTdVBh4ojEoK7PoFJ+7KJQ+0JtD6x9fXlR9r+Cqqv1A6pzOny/1yj6H6YJXgWHlJuuqEFtRyZibmC8sHdFITkTCw97vSNbMLFR2jQfnHAjfsnREh2b/imxCkCuQNh8OP4kJbnLkF2QIGHZQr4CyCodsj40dpGvh96/Wvj4SGuVadinDAbARQlJ0l1mU/9EjpnKoJjF9Uy8yoDJUoyTjYoj2fNI/kxtnR3sLL3TC/a30swPjNX7NstPQpQyfaDFJ2eNtX8JU/xDpMqQlQXkXli5b8W2/lUHd5hFC7ixfb9q6ihQuQYTe4W36eC/KDbHzZLtKdEyr+Fewz5rdLllRpmo/m1KwbHB/CzEF78m1Os1kmD5Dlj6Di0Gw9+GzxhjxywOsonUUp+79eImbE/5F6C2ZHOatTVbTobpOT9izg7Z7C41dC7ch8k/JW8UHRipnfkAmV9YmPa4XvB1QO8L8nK7rDs2cXHbLuKb3Y33w5iFOh+LLtDTogIphQLBjNQFcmuraUu52Rv+YFP0LHp646tnVwBwKGPwl+17yPXCB+gQVYTjsI7LQYcV4Nnv7EZWbxHmqvy/A+pE/y5TJ45yHBWmqIJa3TCn8kf33vvVaLhmgFpJZjXyUww0U=</CipherValue>
  </CipherData>
 </EncryptedData>
</connectionStrings>

Now, all that is left is to test things out and to finish up the process to secure the CD server via the scaling guide.  There’s a lot to that and this article won’t cover it.

Also, as I pointed out before, I did things here based on my own experiences.  There may have been some configuration that was done on the server/network side that I wasn’t aware of, but to the best of my knowledge, this is, step by step, what I did and what worked.  Please feel free to correct something I’ve done or said that should have been done another way.   I’m still learning and just simply wanted to share my experiences.

Happy Sitecoreing!

Sitecore install error: Web Client Service is Unavailable

I recently had a client who was trying to install Sitecore and they got this error.

I know that I’d seen the error before but I couldn’t remember needing to take any special steps to fix it.. or find any issues that resulted from it.  When I tried to find more about it, I also didn’t find a lot of meaningful documentation without a lot of digging around.  Although this is not a serious error.. and really not even an error, it can be distressing to someone who is trying to install Sitecore for the first time.  I thought I would share what it means and what to  do about it.  First off, let me give props to Sitecore Guild for this post which pointed me in the right direction.

As it turns out, this error is happening when Sitecore tries to configure Webdav support during the installation process… which in case  you’re not familiar.. is a protocol that allows for transfer of files via a web share (as opposed to having to map a network drive or using FTP).  The support for Webdav in Windows happens via the web client service and the error is occurring because it is unable to properly configure IIS to support Webdav for your site without the service running.  It happens in particular when you try installing Sitecore on a Windows Server 2008 install because the default installation does not install the Desktop experience package.  This service is not needed for any other purpose other than to enable Webdav, so if you’re not using Webdav, it won’t matter to you at all one way or another.  In Windows XP, Vista and Windows 7 it’s available and enabled and Windows Server 2003, it’s installed .. although it’s not enabled by default.   I suspect that a lot of developers (myself included) probably installed Sitecore for the first time on their own laptop.. which would have it enabled by default.  Once they played with it and felt comfortable, they go to install on the server suddenly this unknown error pops up.

Now.. to answer your next question.. what do you do about it?  The answer, quite simply, is click ok and proceed with the install.  As I said, this is really more of a warning than an error.  Sitecore still configures your installation to support Webdav.  It doesn’t (as near as I can tell) change much of anything with the installation and mostly affects the configuration of the new IIS site.  If you know that you’re going to need Webdav, you have 2 options.  The first is that you can still do nothing.  Webdav requires additional configuration in addition to what is set up during the install process and you can wait til later.  The SDN  has a whole document that covers Webdav configuration, including the steps to enable this service.  If you would rather, you can also back out of the installation and go enable the service and then try installing.  You can find detailed instructions on how to do that here.

Either way, this error is not serious and should not stop you from finishing up with your Sitecore install.  It’s merely a warning about a lack of Webdav support and unless you’re using Webdav, there’s no reason at all for you to worry about it.

Hope this was helpful!  Happy Sitecoring!

Sitecore upgrade assistant?

So welcome to my first post on my Sitecore experiences blog.  As a Sitecore Solutions Engineer, I thought it was about time to start my own blog of my experiences with Sitecore and hopefully be able to pass along some of my own knowledge and experiences to all of you.  What I am currently working on and would welcome feedback on is the upgrade process for Sitecore.

I recently had a customer who was trying to upgrade from Sitecore version 6.1.x and they wanted to get to the current Sitecore recommended version.  In doing so, they would have had to go to the upgrade page and go through the process of mapping out all the individual updates they would need to install to get them to the current version.  We ended up providing them with the update versions to install.. which was done via a manual process of looking at each update and finding out a prereq and then adding that prereq version to the list.  With Sitecore there isn’t a clear upgrade path that allows for you to just install “the upgrade” and be done with it.  Since there are so many different updates and most updates have a prerequisite update that you have to have installed before you can install that update, this can be quite tedious and involve installing 4-5 installs.  This still doesn’t sound like that big a deal until you throw in the fact that you have to walk through about 8-10 steps per upgrade.. which doesn’t include counting all the steps to adding things to .config files.. which can be several steps as well.   To further complicate this, not all upgrades are needed.. only certain ones.. and there are also “optional” steps to the upgrade process.  All of this has to happen and if you miss a step, you could potentially have to start again (i.e. my customer didn’t have a backup for each update and had one of the upgrades gone wrong, he would have had to start at the beginning after doing a restore).  The more complex the Sitecore install is, the harder this whole process is going to be.  I also have to mention that there are lots of modules out there that also have upgraded separately from the actual upgrade.  All of this makes for quite the undertaking and there doesn’t seem to be an easy route to doing all this.

I have decided to try to build a web based upgrade assistant.  No.. it won’t do the update for you… but it will help you in figuring out what you NEED to install.. what files you’ll need to do so.. give you some step by step instructions, provide you with links to modules (at some point.. not sure how easy that will be since there are so many that are 3rd party) and then if there are optional steps to consider in the update, .. it will help you make informed decisions about those decisions (sometimes this is rather complex.. and given that there are going to potentially be a lot of times where a developer isn’t even familiar with Sitecore going into the install process, making these sorts of decisions really ends up requiring some help from Sitecore Support).  So this is my goal.   Wish me luck. 🙂

If you have any feedback or comments.. I welcome them.