Using SharePoint 2007 and SharePoint 2010 with Encrypted Databases

With SQL 2008, a new facility is provided called "TDE" or "Transparent Data Encryption". This is sometimes required by clients whose corporate governance rules require files in a filesystem to be encrypted. What do you have to do to get this working with SharePoint 2007 or SharePoint 2010?

Nothing!

As the name of the feature suggests, you simply have to set it up on the SQL Server side (as per http://technet.microsoft.com/en-us/library/bb934049(SQL.100).aspx), and your underlying database files (and SharePoint Content) and any backups thereof will be encrypted without any extra effort on your part.



DDK

SharePoint Installation/Deployment Best Practice - Using SQL Server Aliases to avoid issues if the SQL Database Server is Renamed, a SQL Instance is added or the SQL Port is changed

One of the problems SharePoint 2007 is that the database name and server name is held in several tables within the configuration database. When you need to change the name of the database server from Central Administration, several references in the configuration tables need to change. There is the hard way and the easy way of doing this as below:

Changing SharePoint Database Server Name - the HARD/WRONG way

Here is the typical process to change the database Server that SharePoint is using:

(see http://blog.sqlauthority.com/2008/09/09/sql-server-error-fix-sharepoint-stop-working-after-changing-server-computer-name/)

1. Move or attach all the sharepoint databases to the new server.
2. Change the location of the config database with
   
   stsadm.exe -o setconfigdb -databaseserver ServerName -farmuser MyUserName – farmpassword MyPassword
3. Delete the Central Administration Site in IIS
4. Re-run the the SharePoint Products and Technologies Configuration Wizard.
5. DONE

Changing SharePoint Database Server Name - the EASY/RIGHT way

The above process is a bit painful - and I never like deleting a core component of SharePoint like Central Admin. 

To greatly simplify migraton if you should need to change the port or the server name of your SharePoint database server, you should instead use SQL Server Aliases for your connection from SharePoint to SQL. To do this, go to a command prompt on all servers (as they all connect to the database) and enter the following to open the SQL Client Configuration Tool (note that it doesn't have an i between the n and the f):

cliconfg

Once this is open, you can add a TCP/IP alias as neccessary, which points to your physical Server name, port or instance. Use this alias name when entering the database server in SharePoint - and you wont' look back! The server name change process is then as follows:

1. Move or attach all the sharepoint databases to the new server.
2. Change the Alias

3. DONE!

DDK

Fix SSIS 2008 Error - The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

When importing a datasource from a flat file today (a csv), (which ran through a Derived Column Transformation and then a Data Conversion Transformation before hitting SQL), I hit the following error in SQL Server Integration Services 2008 (SSIS 2008):

"Data conversion failed while converting column .....The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

The dataviewers and error output SSIS debugging techniques didn't show anything unusual in the data as it was transformed. In addition, this problem only occurred on my float columns, and only when the data in the columns is blank. I did some quick checks to see if there were a few extra spaces in some of the fields and did a TRIM() for the derived column - but the same error kept coming up. I even enabled "Ignore truncation" and it still didn't work.

Information: 0x402090DE at ImportTemplateData, Flat File Source [2917]: The total number of data rows processed for file "C:\WorkforceProfileInitialImport\Q2_2008_162_Data.csv" is 1050.
Error: 0xC02020C5 at ImportTemplateData, Data Conversion [4034]: Data conversion failed while converting column "[8c Override Census Period FTE]" (2050) to column "Copy of [8c Override Census Period FTE]" (5338). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at ImportTemplateData, Data Conversion [4034]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Copy of [8c Override Census Period FTE]" (5338)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of [8c Override Census Period FTE]" (5338)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at ImportTemplateData, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (4034) failed with error code 0xC0209029 while processing input "Data Conversion Input" (4035). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at ImportTemplateData, SSIS.Pipeline: Post Execute phase is beginning.

The underlying issue was that it was trying to convert these blank strings (from the flat file source datatype) into the float datatype and was thereforce constantly failing. The fix is to tick on the "Retain null values from the source as null values in the data flow" and the package then started to run successfully for those columns which had just blank values.