Navigation:  Bases de données > Accéder à une base QDV7 avec SQL Server

QDV7 can connect to SQL Server databases directly using an SQL Server connection.

It can also connect to databases through a web service when isolation is needed. The SQL Server database credentials are only known by the web service. Users connect to the web service using service-specific credentials.

In both solutions, links can be encrypted using SSL certificates.

Prerequisites

You need to install first SQL Server 2008 or greater along with SQL Server management studio. The system must run 64 bits Windows 2008 R2 or greater.

Package can be downloaded from https://www.microsoft.com/en-US/download/details.aspx?id=43351. Choose:

▪SQLEXPR_x64_ENU.exe + SQLManagementStudio_x64_ENU.exe

▪Or simply SQLManagementStudio_x64_ENU.exe which downloads both SQL Server + Management Studio at once

The aforementioned links are for Express version of SQL Server. Other versions of SQL Server can be downloaded from https://www.microsoft.com/fr-fr/download/details.aspx?id=29066

 

Creating and Testing Databases

Creating databases

Run SQL Server management studio and connect to your SQL Server instance. In the Object Explorer:

 

Run QDV7 (version 7.12.448 at least to test the web service which allows credentials isolation). Download it from http://www.iaes-software.com/downloads/Setup_QDV_7_Beta.exe

Under QDV7, go to Data>Databases>Open Databases, select a path and click Server, then SQL Server.

Enter the  Server name, User name and password as in SQL Server management studio, and enter a database name (any SQL Server name).

To create the database, click Create Article.

To test it, proceed as in How to Create/Edit Articles.

 

Adjusting Permissions

To prevent the user from creating databases, uncheck dbcreator in the fouth screenshot.

Clicking User Mapping is a more accurate method:

 

NOTE Keep db_owner checked if you intend to test the web service. When the test is passed, you can revoke permissions.

 

Connecting to Databases using a Web Service

PREREQUISITES

The Web service runs under Microsoft Core .NET 1.0.1 or greater. If it is not installed on your server, install it from https://go.microsoft.com/fwlink/?LinkID=827546

The server must also run .NET Framework 4.6.1 or greater. This framework is usually installed by default under Windows 2012, not under Windows 2008. Download it from https://www.microsoft.com/en-us/download/details.aspx?id=49982

Download the package for the QDV7 Databases web service from http://www.iaes-software.com/downloads/WebServiceForQDVDatabases.exe. Run it on the server to install the web service. All the web service files are located in the Web Service folder (selected at installation).

CONFIGURING IIS

If you don’t have a non-manager application pool:

1.Go to Application Pools and click Add Application Pool…

2.Create a non-managed application pool by providing the following:

3.Check the box to start the application pool immediately or start it afterward by right-clicking it.

4.Right-click Application Polls and select Add Website…

5.Define the web site as shown below. The port is 80 per default; if you provide a different port (e.g., 6001), open it in the firewall (the users will have to append it to the URL):

In place of checking the box Start Website immediately, you can start it later by right-clicking it in the explorer and select Manage Website>Restart.

EDITING CREDENTIALS

The credentials for both client side and server side are defined in the appsettings.json located in aforeselected "Physical path".

They are not encrypted here because they are behind the web site and are not accessible to the end-user. The file contains two or more sections:

Don’t edit the section “Logging”.

The second section includes:

•Credentials for SQL Server instance: Username and Password. They gives permissions on all databases hosted in the instance and accessible with these credentials

•Credentials used by clients to connect to the web service: ServiceUsername and ServicePassword. The clients can access all databases behind the web service with permissions given by SQL Server credentials as above defined.

Restart the application under IIS when you change credentials in the json file.

 

TESTING THE WEB SERVICE

To test the web service, select the path mapped to the web service as you would do with a basic SQL Server connection: the path is < IP address>:<port>\<database name\>.

Now you can open databases, drag/drop articles, insert articles according to permissions given in the json file.

NOTE Access times may be significantly slower than with a direct connection. This results from IIs latency times and conversion time to json. If you experience times greater than x3, please check your IIs configuration.

 

CONNECTING THE WEB SERVICE THRU A PROXY SERVER

The client is connected to the proxy and the proxy connects to the web service. To enter the proxy address, click Web Services in the Paths tab and check first the box Using Proxy:

 

TROUBLESHOOTING

When you can not connect from QDV7 to the web service, get more information about the problem by adding the file DebugWebService.inf to the QDV7 installation folder (by default, C:\Program Files\QDV 7). Regardless of the content of this file, QDV7 provides several message boxes when clicking Test.

 

Encrypting SQL Server connection

To reinforce security and isolate more the SQL server database, you may want to encrypt the link between the clients and the database instance or between the web service and the database instance.

Notice that when using a web service, the client can not directly access the databases instance so it’s probably not necessary to also encrypt the SQL link to the instance.

You need at least a professional version of SQL Server to use SSL certificates.

Please refer to following topic to encrypt a connection: https://msdn.microsoft.com/en-us/library/ms191192.aspx