Login Authentication methods for Microsoft SQL Server



September 2003

This document demonstrates how to connect to Microsoft SQL Server using both SQL Server authentication and Windows Domain authentication methods, and how to connect from Acu4GL for Microsoft SQL Server and AcuSQL using both authentication methods.

Microsoft SQL Server authentication.
SQL Server authentication ensures that the username and password is checked against a login within the Microsoft SQL Server database. So, to use this login authentication you will need to create a user account as follows with the SQL Server Enterprise Manager:

sql_server_login.gif

Windows Domain Authentication.
This is Microsoft's preferred login authentication, as the username and password used is one that is used to log into the Windows Domain network, and therefore is more secure than the SQL Server authentication, and you will not need to create a separate login within SQL Server. Please see below screenshot on how to specify a Windows authentication login from within the SQL Server Enterprise Manager:

sql_server_login2.gif

Acu4GL for Microsoft SQL Server.
When connecting from Acu4GL for Microsoft SQL Server, the Runtime expects to use the SQL Server authentication, so if there is not a account created within MS SQL Server for the user that is attempting to login, then the login will be rejected.

To switch to the Window Domain authentication, the following Acucorp Runtime configuration variable needs to be set in the application's configuration file (the following is extracted from the Acu4GL User's Guide):

A_MSSQL_NT_AUTHENTICATION
The A_MSSQL_NT_AUTHENTICATON configuration variable indicates whether Microsoft SQL Server will authenticate users based on their Windows login.

If this variable is set to "True" (on, yes), the Acu4GL for Microsoft SQL Server interface attempts to log users on using the SQL Server Windows NT authentication mode. (See your SQL Server documentation for information this authentication mode.) When A_MSSQL_NT_AUTHENTICATION is enabled, the A_MSSQL_LOGIN and A_MSSQL_PASSWD configuration variables are no longer needed or used. (They are still available if you are not using Windows NT authentication mode.) If you have not set up SQL Server itself to allow this type of authentication, setting this to TRUE causes all login attempts to fail. See your SQL Server documentation for information on how to set up this type of authentication.

The default is "False" (off, no) indicating that the Acu4GL for Microsoft SQL Server interface will not use Windows NT authentication mode when logging users on, and will continue to use login/password information.

AcuSQL and AcuSQL for Microsoft SQL Server.
The following Embedded SQL is required to create a connection from the AcuSQL Runtime to the Microsoft SQL Server database by using the following syntax:

    CONNECT TO <odbc_dsn> USER <username> USING <password>

When using the AcuSQL for Microsoft SQL Server Interface (ASQL_RUNTIME_DLL is set to "ASQLSRVR.DLL"), you do not need to pass the username and password, as the AcuSQL Runtime will use the current user’s information that is logged into the Windows Client machine:

    CONNECT TO <servername>

If the Microsoft SQL Server security does not contain a SQL Server or Windows Authentication login, then the connection will fail for the specific user that is attempting to connect to the SQL Server database.

Your Session will expire in 90 minutes
Notification will be shown in:
600 seconds