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 select SQL Server

Connection Parameters

connection SQL parameters v3.1.0

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

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.

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.

connection test OK

If the connection is invalid, the following message appears :

connection test not OK

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

Last modified: Apr 18, 2023

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment