Tips and Tricks from R.J., the SalesLogix Consultant

How to Fix SQL Server Connection Issues

Tweet

 

Many SalesLogix LAN installers are having trouble connecting the Network Client to the SLX OLE DB Provider. Since SQL Server 2005 was released, any client access to SQL Server requires the SQL Server Native Client to be installed on the client machine. This harkens back to the SQL Server 7 days of the SQL Server Client install from the SQL Server SERVER CD (which warned you that you were trying to install a Server product on a Desktop machine before giving you the option of only installing the SQL Server Client).

For some reason, installers are not installing the SQL Server Native Client. Trust me, this is required. Period. Full Stop. Do not pass Go.

Depending on the client machine’s operating system - running in either 32 bit or 64 bit mode -you will download the x86 or 64 bit version of the Native Client, in turn based on which SQL Server database version you are accessing!

Here are the three flavors of the SQL Server Native Client:

1. SQL Server 2005 Native Client download page
2. SQL Server 2008 Native Client download page
3. SQL Server 2008 R2 Native Client download page

There is a huge difference between SQL Server 2008 and 2008 R2, so do not download the wrong version of the Native Client and do not mistake the server’s operating system for the SQL Server version being accessed!

All of this is in the SalesLogix Implementation Guide, so RTM (yea, like most of us I rarely do).

(Additional Resource) Hints from Heloise:

1. Make sure you are talking "2008" or "2008 R2" SQL Server! This makes a world of difference.
2. You must install the appropriate MS SQL Server Native client on every machine that will access/touch the SQL Server. This is mandatory (i.e., required) and includes every PC\server. This is a Microsoft thing, by the way.
3. When you alter the SalesLogix Connection (SLX OLE DB Provider) you must (required by Microsoft) select Persist Security = true and Integrated Security = <nothing>. This is required, even if it looks like it is set correctly. Do not retest the connection; click on "OK" to close the window. So...
• Setup the connection
• Test
• Change Persist Security setting to True
• Change Integrated Security setting to blank
• Click on "OK." To close the data connection setting window.
4. Open up the ports 1706, 1433 (turn off Windows Firewall to test) on both DB and Application Server. Add SLXSystem and SLXProvider to safe applications for network traffic.
5. Additionally, you must install the SQL2005 native client on your SalesLogix Server (where you are installing the SalesLogix Connection Manager, the OLE DB Provider). If you are having trouble with the connection, install both the 2005 and the appropriate 2008/2008R2 native client. It doesn't hurt.
6. When you set up the SalesLogix Connection manager, link to the correct Microsoft SQL Server Native client! 
   SQL2008 use v. 10.0
   SQL2008R2 use v. 10.1
   (I've had many SalesLogix Administrators ask me why they can't connect to their SQL 2008 R2 server using the Native Client v 10.0.)

FAQ: Where are SalesLogix Picklist Names stored?

That's a good question as there is no Picklist Master table storing the Picklist "parent" record. Instead the Picklist Name is stored where the PICKLISTID = 'PICKLISTLIST'; therefore, the 12 characters long ID value.

Run this in SQL Query Analyzer to list all of your Picklists in alphabetical order:

SELECT ITEMID as [The PickListID], [TEXT] as [PickList] FROM sysdba.PICKLIST WHERE PICKLISTID = 'PICKLISTLIST'
ORDER BY [TEXT]