Connecting to SQL Server
Description
Alpha Anywhere AlphaDAO provides an extension driver for SQL Server to simplify building connections using any of the SQL Server ODBC drivers provided by Microsoft. You must first install the appropriate driver for the version of your SQL Server database. In the connection string dialog, select the version of the database you are connecting to. Alpha Anywhere will select the installed driver that is the best fit for the target version.
Before you Begin
Before you begin, make sure you have installed and configured your SQL Server server and installed the appropriate client side driver. For more information on installing and configuring SQL Server, see https://www.microsoft.com/en-us/sql-server/sql-server-downloads.
Alpha Anywhere is a 32-bit executable. You must install the 32-bit ODBC client driver for your database. To view those drivers, you will need to run the 32-bit ODBC Data Source Administrator found at c:\windows\syswow64\odbcad32.exe.
Using the SQL Server Connection String Dialog
When you select SQL Server as your connection type in the connection string dialog, the dialog below will be displayed. Select the version of your database, enter the server name and provide any authentication information required to connect to your database. The values you select will be generated into the connection string at the top of the dialog for you as you make changes.
Microsoft documentation identifies "ODBC Driver 18 for SQL Server" as the current driver for "all supported versions" of SQL Server. With the exception of some of the oldest database servers, you should be able to install this driver and successfully connect to and query your database.
If you get errors or unexpected results when executing SQL, try downloading and installing one of the older drivers listed in SQL Server Versions.
ODBC Driver 18 for SQL Server changes the default behavior for encryption. Previously it was disabled by default. In ODBC Driver 18 for SQL Server it is enabled by default.
When connecting to a test database, be sure to set the Trust Server Certificate to checked ('A5TrustServerCertificate=Y') if you have not installed a certificate or are running with a self-signed certificate.
For production systems, especially those which you connect to on a public network, you should always purchase and install a certificate that can be publicly validated, set Encrypt to checked and set Trust Server Certificate to unchecked.
When running any version of SQL Server, be sure to update the database software to keep current with Microsoft bug fixes and security patches.
Because secure connections to the database rely on Windows infrastructure, you should also keep your version and patch level of Windows as current as possible.
In the past, it was acceptable, if not advisable to skip applying patches to a working system. This is no longer the case because of the need for constant security updates. Instead, you will need to watch for frequent updates and test them on your system.
SQL Server Versions
The list below shows the database version and the previously recommended driver for that database. The version that you see in the Microsoft ODBC administrator dialog is for the client driver of that name specifically. It is independent of the database server version. You may need to update your client driver from time to time as updates are made available from Microsoft.
Again, the client driver name is the one that you want to match the database version.
As of this writing the following client drivers are available for the various versions of the SQL Server database.
- SQL Server Version
- Client Driver Name
- 2019,2022
ODBC Driver 18 for SQL Server
- 2017
ODBC Driver 17 for SQL Server
- 2016
ODBC Driver 13 for SQL Server
- 2014
ODBC Driver 11 for SQL Server
- 2012
SQL Server Native Client 11.0
- 2008
SQL Server Native Client 10.0
- 2005
SQL Native Client
- Older Versions
SQL Server
Consult the documentation for Microsoft SQL Server for more information about downloading and installing client drivers.
Configuring the Connection String
The SQL Server connection string dialog has tab pages for general properties, timeouts, SSH tunneling, and advanced properties. Each of the properties are described below.
The Timeouts tab page lets you override default timeouts for login, the lifetime of the connection and for each query.
The SSH tab is used to enable a connection to a database using an SSH2 tunnel. These connections are made from a local tunnel that is connected to a remote server and then finally to the actual database, typically behind a firewall. Alpha Anywhere will create and manage the tunnel for you based on the values you provide on this page.
The Advanced tab is used to set up a secure connection, enable column encryption, enter additional parameters and provide an initial command to be executed at connect time.
To set SQL server parameters that are not directly supported by the SQL Server connection string dialog, enter them as comma separated key=value entries. They will be passed directly to the connection string provided to SQL Server.
To execute one or more commands immediately after connecting to the database, enter native SQL Server commands separated by a semi-colon.
SQL Server Connection String Properties
- Connection Property
- Description
- A5API
Setting the connection property A5API to 'SQLServer' will load the SQL Server driver when you open a connection.
- A5DBVersion
The version number of SQL Server your server is running. This will assure that your client connects using the driver best matched to your SQL Server server.
- Server
Enter the DNS name or IP address of your server.
- Port
SQL Server defaults to an IP address of 1433. It is a good practice to set the server port to a different value so that it is not as obvious what port SQL Server is running on. This is not a substitute for using secure connections.
- UserName
Set this value to the user name you wish use to connect to the database. This is only needed if trusted_connection is not set to 'yes'.
- Password
Set this value to the password value you wish use to connect to the database. This is only needed if trusted_connection is not set to 'yes'.
Using the connection string dialog will obscure the password within the connection string. Publish functions encrypt the connection string.- Database
Enter or select the name of the database you are targeting.
- A5TraceSQL
Set this value to Y to trace SQL statements. Within Alpha Anywhere messages are written to the trace pane. For Application Servers the output is written to a file. The actual location varies by server.
- A5TraceSQLLevel
Set this value to determine how much detail will be written to the trace log. When set to "Errors", only errors will be reported. When set to "SQL" a summary of each statement and the results will be reported, including errors. When set to "Complete" all errors and SQL will be reported as well as key function calls and translated queries with argument markers as well as any caller context provided. When set to "Diagnostic", all of the previous output will be included along with the user thread name, the argument values and bind mappings. All other values are treated as if "SQL" was set.
- A5TraceSQLFormat
Set this value to indicate the output format desired. When set to "JSON", information will be written in JSON format with a trailing comma. Parsing a file in JSON format can be done by stripping the last trailing comma and wrapping the text in square brackets "[]". When set to "Text", the output is written as formatted text. This is the default format.
- A5LoginTimeout
Set this value to control the timeout for new connections in seconds. Omit this setting to use the SQL Server ODBC driver default value.
- A5ConnectionTimeOut
Set this value to control the timeout for the lifetime of a connection in seconds. Omit this setting to use the SQL Server ODBC driver default value.
- A5QueryTimeOut
Set this value to control the timeout for a single query in seconds. Omit this setting to use the SQL Server ODBC driver default value.
- A5SSHTunnel
The connection string properties for SSH connections are discussed separately in Connecting Securely with SSH.
- A5SSL
Set this value to 'Y' to encrypt connections to the server. The default value of this parameter is 'N'.
- A5TrustServerCertificate
Set this value to 'Y' if you are using a self-signed certificate and do not want to check the server identity against a certificate authority. This value should never be set to 'Y' on a production server, but can be useful in testing out secure connections on a development server. The default value of this parameter is 'N'.
- A5EnableColumnEncryption
Set this value to 'Y' to enable column encryption for the Always Encrypted feature of SQL Server. This will add the string "ColumnEncryption=Enabled" to the SQL Server connection string. For more information on this feature see https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine. The default value of this parameter is 'N'.
- A5ForceStringsToUTF
Set this value to 'Y' to disable functions that send data to the database as wide characters (16-bit Unicode). Data will instead be bound and passed as multi-byte characters. The default value of this parameter is 'N'.
- A5ANSINullPadWarn
This property is obsolete.
- A5InitialCommand
Set this value to the semi-colon delimited list of commands you want to execute after a connection is opened.
Using Portable SQL with SQL Server
A SQL Server connection automatically selects SQL Server as it's syntax. The SQL Server syntax handler reads table information, and generates DDL (Data Definition Language) such as CREATE TABLE, DROP TABLE and DML (Data Manipulation Language) such as SELECT, INSERT, UPDATE, DELETE. It is also responsible for translating AlphaDAO Portable SQL functions into native implementations.
Using Datetime Types with SQL Server
SQL Server has two datetime types.
DATETIME - The datetime type is an older version of datetimes. It records fractional seconds in 'ticks'. A tick is 1/300 of a second. For that reason, precision of fractional seconds is imprecise.
DATETIME2 - Datetime2 is a newer type that supports fractional values from 0 to 7 digits to the right of the decimal point.
Xbasic supports up to 4 decimal places in some places (100 microseconds), but generally supports 3 decimal places (milliseconds).
SQL Server Compatibility Levels
SQL Server has a setting for determining database behavior called Compatibility Level. This is a complex setting and may affect a number of behaviors. Compatibility Level can be used to determine how datetime types will be handled in SQL Queries.
150 - The current compatibility level for new databases using SQL Server 2022 is 150. This will cause datetime values passed in arguments to be processed as DATETIME2 types.
120 - Setting the compatibility level of the database to 120 will cause arguments to be processed as DATETIME types.
To determine current compatibility level setting of databases in SQL Server, run the following query:
select name, compatibility_level from sys.databases;
To set the compatibility level setting for a specific database (Northwind in this example) in SQL Server, run the following query:
alter database Northwind set compatibility_level = 120;