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” :
This is the address of the SQL Server instance you need to access. Possible values are :
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>,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.
“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 note on Windows Authentication
When published in Alpana Server, the Windows user will be that configured in the IIS site :
IIS APPPOOL\Alpana3App (see the Server Architecture chapter).
This is because there are 2 different threads owned by 2 different Windows users :
- the Designer is in a thread owned by your current Windows user
- the Server is in a thread is owned by the IIS application pool identity :
In order to allow Windows authentication on published Dashboards to a database X on SQL Server Y, you need to :
- make sure the
IIS APPPOOL\Alpana3AppSQL user exists on SQL Server Y, and that the corresponding Windows user is bound. If you installed Alpana Server with SQL authentication, or if SQL Server Y is on another SQL Server, then you will have to manually create this user in SQL.
- make sure the
IIS APPPOOL\Alpana3AppSQL user has the required permissions on database X. For example, this user will need
db_ownerpermissions to run stored procedures or functions.
The name of the database to connect to.
This lets you select the database name from the list of databases.
This lets you write the database name as free text.
This is useful for creating a connection to a database which you cannot access while in the Designer.
For example when the database name is different in the production environment.
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 ?)
The SQL Server Connector fetches the following object types in the database schema :
- Stored Procedures
- Table-Valued Functions
(new in version 2020 R2)
The SQL Connector allows to fetch data from objects belonging to any Database Schema, not just
If you need to restrict the schema, see chapter getting and browsing the Connection schema.
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