MSCRM: Installing - the instance name must be the same as the computer name

When installing Microsoft Dynamics CRM MSCRM 4.0 you get the error "the instance name must be the same as the computer name" from the installation wizard Microsoft SQL Server System Requirements check.

This may be because 1) the server name has been changed since SQL Server was installed, or 2) you are running a 64-bit or Windows Server 2008 environment.

Note that in the Microsoft Dynamics CRM 4.0 "Help", it may state that only SQL Server 2005 is supported. This is not the case; for more information please see the Microsoft Knowledgebase article "Support for running Microsoft Dynamics CRM 4.0 together with Microsoft SQL Server 2008".

To check if the server name has been changed since SQL Server was installed, open Microsoft SQL Server Management Studio and run the following query:
SELECT * FROM sys.servers

If the current server name is not listed, then use the following procedures to correct the issue:
Execute sp_dropserver <OldServerName>
Execute sp_addserver <NewServerName>, local

If you are running a 64-bit or Windows Server 2008 environment, then check that Named Pipes have been enabled in the SQL Server Network Configuration (Start menu All Programs Microsoft SQL Server Configuration Tools SQL Server Configuration Manager expand SQL Server Network Configuration highlight Protocols for <SQLInstanceName> Enable Named Pipes.

DEVELOPMENT: Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server

Running a query from SQL Server 2008 over a linked server connection to a SQL Server 2000 server causes the following error:

OLE DB provider "SQLNCLI10" for linked server "mylinkedserver" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "mylinkedserver" returned message "The stored procedure required to complete this operation could notbe found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider interface, but returns a failure code when it is used.

Or

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "". The provider supports the interface, but returns a failure code when it is used.


Firstly, SQL Server 2000 SP4 (service pack 4) must be installed.

Then the system stored procedures must be manually upgraded.

Note that when manually upgrading the system stored procedures, we used SQL Server Authentication mode which requires the syntax:
osql -U [adminlogin]-P [adminpassword]-S [linkedservername]-i [location]\instcat.sql

We first entered the line as:
osql -U sa -P myPassword -S myServer -i C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql

But this just bought up the osql help /? list of parameters. To avoid this, make sure that the -i path is in speech marks!
osql -U sa -P myPassword -S myServer -i "C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql"

Note that the manual upgrade takes a couple of minutes to run and there are a whole bunch of message and numbers displayed in the command window as it executes. If the process is successful, then the last but one line will read "instcat.sql completed successfully".

In our experience, the SQL Service did not need to be restarted, the fix worked immediately and we were able to query the SQL Server 2000 server from SQL Server 2008 over the link immediately:
SELECT COUNT(*)
FROM myLinkedServer.myDatabase.dbo.myTable

We hope that this tip helps others new to SQL Server 2008 development!

Please see our website for more information on our software development services and software development resources.

DEVELOPMENT: rsReportServerDatabaseUnavailable on Reporting Services 2008

Trying to connect to the SQL Server Reporting Services reports server for the first time using http://MyServerName/Reportserver, we got the following error:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help. SQL Server does not exist or access denied.

Using http://localhost/Reportserver opened a login dialog, but the administrator username and password didn't work.

Discussing this with the company who supplied the server, it came to light that the server name had been changed after SQL Server Reporting Services had been installed.

We resolved this by using the rsconfig utility:

Opening a Command Prompt and executing a line like this:
rsconfig -c -sMyServerName -dReportServer -aWindows -uDomain\Account -pMyPassword

Followed by resetting IIS (Internet Information Services)
iisreset

We hope this helps!

DEVELOPMENT: SQL Server 2008 SP1 released

SQL Server 2008 Service Pack 1 (SP1) is now available. You can use these packages to upgrade any SQL Server 2008 edition.

Note: [Microsoft] remain committed to our plans to keep service packs contained, focusing on essential updates only, primarily a Roll-up of Cumulative Update 1 to 3, Quick Fix Engineering (QFE) updates, as well as fixes to issues reported through the SQL Server community. While keeping product changes contained, [Microsoft] have made significant investments to ease deployment and management of Service Packs:

• Slipstream – You are now able to integrate the base installation with service packs (or Hotfixes) and install in a single step.
• Service Pack Uninstall – You are now able to uninstall only the Service Pack (without removing the whole instance)
• Report Builder 2.0 Click Once capability

For more information about SQL Server 2008 Service Pack 1, please review the Release Notes.

For more information about Hilltops IT's software development services, please see our website or contact us direct on +44(0) 1782 564252.

MSCRM: Microsoft CRM Authentication Error 0x80040204

It's been a challenging afternoon today, following one of our clients introducing a new server to their network domain. Users trying to log into the Microsoft CRM system (hosted on another server) got the error: "Authentication Failed" with code 0x80040204.

The introduction of the new Microsoft Windows Server 2008 machine somehow affected the existing Microsoft Windows SBS 2003 domain controller, and changed (or recreated) the Active Directory groups which Microsoft CRM relies on: PrivUserGroup, ReportingGroup, SQLAccessGroup and UserGroup.

In the Application Event Log, errors were reported from the SCRMDeploymentManagerSnapin: "IsCRMSysAdmin : WhoAmI failed".

Checking the Microsoft CRM database, we could see that all System Users and Roles were still there, so the problem seemed to point towards Windows security and / or Active Directory.

We've found in the past that the best way to tackle these types of issue is to roll your sleeves up and get straight into the Microsoft CRM error tracing feature. Details of how to set this up are here: http://support.microsoft.com/kb/907490

Setting TraceCategories to "*:Verbose" will give you absolutely everything that's going on to understand the problem, but we strongly recommend only enabling this temporarily.

The Trace output file showed the SQL statements for the login; copy/pasting these into a query editor showed record being returned, so no problem there. the problem was found about 20 lines down in the Trace: "Invalid code for CRM error", "ADsGetObject() failed". Looking more and more like Active Directory.

Microsoft CRM relies on the PrivUserGroup, ReportingGroup, SQLAccessGroup and UserGroup Security Groups, so we checked the details of the GUIDs in Active Directory against those registered in the Microsoft CRM database.

Viewing the Microsoft CRM values can be done by running the following in a query editor:
SELECT UserGroupID , PrivilegeUserGroupID, ReportingGroupID, SQLAccessGroupID
FROM OrganisationBase

To view those in Active Directory, you'll need to use adsiedit.msc (part of the Windows Support Tools on the Windows Server installation CD-ROM or download it from http://download.microsoft.com, search for "adsiedit.msc") and view the objectGUID value for each of these groups in hex.

The values from Active Directory need to be manipulated slightly, but they should match those in the Microsoft CRM database: first reverse the first four groups of characters, reverse the next two groups of characters, reverse the third group of two and copy and paste the fourth group of two and the final group of six (the last two groups are not reversed). Note that you do not reverse each pair of characters individually, but treat each pair as a group and reverse the groups as shown below.

So, for example:
objectGUID=0x 1x 2x 3x 4x 5x 6x 7x 8x 9x Ax Bx Cx Dx Ex Fx

Becomes:
GUID in database={3x2x1x0x-5x4x-7x6x-8x9x-AxBxCxDxExFx}

The values we found didn't match, so it was just a simple SQL UPDATE statement to set them right:
UPDATE OrganizationBase
SET UserGroupID='GUID', PrivilegeUserGroupID='GUID', ReportingGroupID='GUID', SQLAccessGroupID='GUID'

Where 'GUID' is the appropriate manipulated Active Directory objectGUID.


As an example, the specific values we had in Active Directory for the four groups were:
A7 7D FB B8 8B 85 8C 47 81 F0 36 D6 F0 89 8A F7
CB F5 E6 71 58 B6 19 4A BA 56 CC 49 67 0C 58 FF
C5 CC DE B9 8A 72 DD 48 A6 4E 5E 4D D4 64 DD 0E
18 F7 97 EF 41 B9 C8 4E AA F8 3B 2D CE 02 EC A2

So manipulated as above they became:
B8FB7DA7-858B-478C-81F0-36D6F0898AF7
71E6F5CB-B658-4A19-BA56-CC49670C58FF
B9DECCC5-728A-48DD-A64E-5E4DD464DD0E
EF97F718-B941-4EC8-AAF8-3B2DCE02ECA2

Our SQL statement looked like this:
UPDATE OrganizationBase
SET UserGroupID = 'B8FB7DA7-858B-478C-81F0-36D6F0898AF7'
, PrivilegeUserGroupID = '71E6F5CB-B658-4A19-BA56-CC49670C58FF'
, ReportingGroupID = 'B9DECCC5-728A-48DD-A64E-5E4DD464DD0E'
, SQLAccessGroupID = 'EF97F718-B941-4EC8-AAF8-3B2DCE02ECA2'

Many thanks to Brian Reid for his blog post here - http://www.c7solutions.com/blog/2008/04/crm-30-disaster-recovery.aspx which was really helpful in getting us through this issue.

DEVELOPMENT: Fortnightly digest - what's happening at Hilltops IT and the world of software development

We've been writing on the blog now for several weeks about the SQL Server Reporting Services bespoke Business Intelligence reporting bespoke development project which we have been developing. This also involved some SQL Server Integration Services work - the two go hand-in-hand in many ways for larger reporting database projects.

For anyone interested in SSIS, there is a useful introductory article here which explains many of the concepts: http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_1_p1.aspx



For further information on our website and software development services, please do not hesitate to contact us on +44(0)844 357 7360 or +44(0)1782 564252 or email development@hilltopsit.co.uk

We look forward to hearing from you!

DEVELOPMENT: Fortnightly digest - what's happening at Hilltops IT and the world of software development

Hilltops IT Developer's diary

Since last fortnight's post, most development activity has been on the following projects:

• Website development - development work on our Hilltops IT website for re-branding. this has been the main focus for us - bringing together the design work with the "nuts and bolts" of the mechanics of the site. there are still a couple of tweaks to make, but the site is looking really good. Everyone internally is really excited about being able to announce the launch.


• Business Intelligence reporting - continued work on a bespoke project reporting on 3,000,000+ rows of data from disparate data sources using SQL Server Reporting Services.

Industry News

Development tools component makers see 2009 as a big year for MS platforms

Introduction: The gears are turning for implementations of Microsoft's next generation of platform technologies, and as a matter of course, component makers are the leading-edge innovators. In 2009, some of the latest Microsoft technologies will be gaining the most traction as Silverlight's star rises and developers rethink how they structure Web applications. Silverlight is rapidly becoming a popular platform for component developers to target. ComponentArt, ComponentOne, Developer Express and Infragistics are all making controls for creating business applications and filling in functionality that Microsoft has omitted.

See the full article here: www.sdtimes.com/link/33194

So as Microsoft Partners, leading edge software and website developers and providers of Microsoft platform-based business solutions, Hilltops IT are perfectly placed to take advantage of these new innovations.

Free e-book Introducing SQL Server 2008

Introducing Microsoft SQL Server 2008 by Peter DeBetta, Greg Low, and Mark Whitehorn

ISBN: 9780735625587

Learn about major new features in SQL Server 2008 including security, administration, and performance.

Introducing Microsoft SQL Server 2008:

• Chapter 1: Security and Administration
• Chapter 2: Performance
• Chapter 3: Type System
• Chapter 4: Programmability
• Chapter 5: Storage
• Chapter 6: Enhancements for High Availability
• Chapter 7: Business Intelligence Enhancements

Download it here: http://csna01.libredigital.com/?urss1q2we6

For further information on our website and software development services, please do not hesitate to contact us on +44(0)844 357 7360 or +44(0)1782 564252 or email development@hilltopsit.co.uk

We look forward to hearing from you!

DEVELOPMENT: issue with DateTime datatype in SQL Server Reporting Services

We recently upgraded a client’s SQL Server 2000 business intelligence reports to SQL Server 2005, and from a Visual Studio .net 1.1 software development environment to Visual Studio 2005.

It should be a simple transition we thought, which it was… apart from some anomalies around the datetime parameters in SQL Server 2005 Reporting Services.

There were a couple of things we noticed going wrong. Firstly, defaulting a value to 2/11/2008 (dd/MM/yyyy) for example would come through on the report as 11/2/2008. Secondly, when changing the date from 11/2/2008 using the datepicker to 12/2/2008 would display 2/12/2008 in the textbox. Basically, the dd/MM/yyyy was getting transposed to MM/dd/yyyy and back again.

Also there were "the value provided for the report parameter [parameter name] is not valid for its type" selecting the dates and running the report when dates like 13/2/2008 or 2/13/2008 were selected.

Searching the internet we found a number of reports of similar issues with several different fixes being required. Unfortunately no blog or knowledgebase article seemed to have all the information in one place, so we have centralized the details of fixes here.

We hope that you find it useful.


Report Server changes

1) First, ensure that all up to date SQL Server Service Packs are installed – there are reports that the problem for server-side issues were fixed in Services Pack 1 but the reintroduced in Service Pack 2. At time of writing the issue is understood to be fixed with patch releases to date.

SQL Server Service Packs and Hot Fixes can be found by using Windows Update or the Microsoft Download Centre: http://download.microsoft.com/

2) Next, we need to change (or add) the Culture value in Report.aspx to:
<%@ Page language="c#" Codebehind="Report.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.UI.ReportWrapperPage" EnableEventValidation="false" Culture="en-GB"%>
<%@ Page language="c#" Codebehind="Report.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.UI.ReportWrapperPage" EnableEventValidation="false" Culture="en-GB" %>%@ Page language="c#" Codebehind="Report.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.UI.ReportWrapperPage" EnableEventValidation="false" Culture="en-GB" %

Report.aspx can be found in C:\Program Files\Microsoft SQL Server\MSSQL.?\Reporting Services\ReportManager\Pages\

3) Finally, change the culture in the IIS to "en-GB": Control Panel \ Administrative Tools \ Internet Information Services (IIS) Manager \ Properties \ ASP.NET tab \ Edit Configuration button \ Application tab \ Globalisation Settings section \ Culture



Report Definition changes
The blog entry we found on this said to ensure that the language for the report itself is set to your country (Layout view, Report Properties). We couldn’t find this place in the UI to change this, so instead did a Find and Replace on “en-US” for “en-GB”.

Changing the Language and redeploying the reports should now fix the problem for the user. If however the users still get problems, then ask them to check the Language Settings of their browser are “English (United Kingdom) [en-gb]”



Development machine changes

Ok – so you have fixed the problem for your users, but Visual Studio still doesn’t behave quite right. We need to change the Regional Settings slightly.

First close visual studio, then change the Regional Settings on your development machine. Customize the short date format to "yyyy-MM-dd" (Control Panel \ Regional and Language Options \ Regional Options \ Customize button \ Date) This fixes the report Preview from Visual Studio.

Note that this may cause problems with other applications. If anyone has a better fix for this particular issue, please email development@hilltopsit.co.uk and we will update this post.