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 note on Windows Authentication

!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.
This will result in Data Retrieval Error messages.

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 : IIS APPPOOL\Alpana3App
  

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\Alpana3App SQL 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\Alpana3App SQL user has the required permissions on database X. For example, this user will need db_owner permissions to run stored procedures or functions.
  

Database

The name of the database to connect to.

Detection

This lets you select the database name from the list of databases.

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

Manual

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.

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
  

Database Schema

(new in version 2020 R2)

The SQL Connector allows to fetch data from objects belonging to any Database Schema, not just dbo.
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

Code192 2020 All rights reserved