Additional information and resources for Microsoft SQL Server

     If necessary, you can download the Microsoft SQL Server JDBC Driver from this location.  Several versions are available.  Make sure that you download a driver version that is appropriate for your Windows system, Java version and Microsoft SQL Server database.  At time of writing the 4.2 version requires Java 8.  Unless you are already running JVM at version 8 on your Windows server, we recommend version 4.1.  Downloads of other JDBC driver versions may also be available:

     Download: Microsoft SQL Server JDBC Driver

• The Microsoft SQL Server JDBC Driver version 4.1 is implemented in the file sqljdbc41.jar.  You may need to copy the file to the /jar directory of your JSM instance and restart the Java Service Manager before using the driver with Transformation Maps in LANSA Composer.

• If you are using integrated Windows authentication to connect to the Microsoft SQL Server database, then it may also be necessary to copy the appropriate version of the file sqljdbc_auth.dll into your /Windows/System32 directory. 
  
  Although versions of this file for 32-bit and 64-bit Windows environments are supplied and installed with the Microsoft SQL Server JDBC Driver, please note that, for LANSA Composer version 6 and later, it is the 64-bit version that is required and it must be installed in the /Windows/System32 directory.
  
  Refer to the driver documentation for more information.

• With respect to specifying the port number (or not) in the connection string, at this link - Building the Connection URL - you will find this note:
  
  "For optimal connection performance, you should set the portNumber when you connect to a named instance. This will avoid a round trip to the server to determine the port number. If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored."
  
  While specifying the port number may offer optimal performance, the last sentence of that note also reveals a subtle danger.  If you specify the port number incorrectly, you may not connect to the SQL Server instance that you expect.  (In some SQL Server installations, static port numbers are not assigned, for security reasons.)  In the event that you are having connection difficulties, try removing the port number from the connection string, leaving only the SQL server instance name. 

• If you are using integrated Windows authentication to connect to the Microsoft SQL Server database, then you need to think about "who" the Windows user is that will be used for Windows authentication.  The Transformation Map is executed in the JSM service.
  
  For more information on the JSM service, refer to Services Used by the LANSA Composer Windows Server.
  
  So the user will be the user under which the JSM service is running.  It may be necessary to alter the startup options for the JSM service to use a specific user account (eg: PCXUSER).  You should make sure that the user has the necessary authorities in the SQL Server target database.

• Refer to relevant Microsoft resources for more information on the JDBC drivers available for Microsoft SQL Server and the keywords and values that can be specified in the JDBC connection string.  Following are some useful references.  Note that some of these references relate to specific versions of Microsoft SQL Server and associated software.  You should refer to the equivalent pages for the software versions you are using.

Overview of the JDBC Driver

Building the Connection URL

Setting the Connection Properties

ConnectionStrings.com - Forgot that connection string? Get it here!