SQL Server

Versions

This connector can be used to get data from local or remote Microsoft SQL Server installations with the following versions :

• Microsoft SQL Server 2012 and later
• Microsoft SQL Server Express 2012 and later
  

Selecting Connection Type

In the “Create Connection” dialog change the Connection “Type” by selecting “SQL Server” :

Connection Parameters

Server

This is the address of the SQL Server instance you need to access. Possible values are :

• (local) or localhost : local server
• <NetBIOSName> : server named with a NetBIOS name
• <IPv4Address> : server accessible through a IP v4 address
• <IPv6Address> : server accessible through a IP v6 address
• <server address>\<instance name> : connect to a specific instance. For SQL Express, it may be necessary to use <server address>\SQLEXPRESS
• <server address>,1433 : 1433 is the default port for SQL Server, but can be changed like this
  Be aware that when searching for a server instance, Alpana waits for a response. If the server instance doesn’t respond, there is a rather long timeout.
  

Authentication Mode

“SQL Server” : will use SQL Server authentication with “User name” and “Password”.

“Windows” : will use Windows Authentication from the Windows user that generates the query.

!Important :
Windows Authentication will use a different Windows user when in Designer and when published in Server. That Windows user may not have the required access to SQL Server.
When published in Server, the Windows user will be that configured in the IIS site IIS APPPOOL\Alpana3App.
For example, this user will need db_owner permissions to run stored procedures or functions.

Database

The name of the database to connect to.

After changing credentials, you can fetch again a new list of database names, by clicking the refresh icon.

Test Connection

To test the connection for validity, click “Test Connection” button. The following confirmation message will confirm its validity.

If the connection is invalid, the following message appears :

Then you will need to fix the Connection parameters (credentials, server name, …) or check network connectivity (is the SQL Server accessible from here ?)

Connection Schema

The SQL Server Connector fetches the following object types in the database schema :

• Tables
• Views
• Stored Procedures
• Table-Valued Functions
  

Data refresh and Buffer

The SQL Connector is not bufferized by default.

However, when data is joined with another Alpana Connection (including another SQL Connection), data is bufferized.

If you need to get new data, see Chapter The data Buffer

Code192 2020 All rights reserved