Ce document décrit les instances utilisateur dans SQL Server 2005 Express Edition et la façon dont vous pouvez les utiliser pour simplifier l'ajout de fonctionnalités de base de données à vos projets Visual Studio. (11 pages imprimées)

Retrouvez la version anglaise de cet article sur ce lien.

Cet article s’applique à >SQL Server 2005 Express Edition.

Sur cette page

Introduction
Association de fichiers de base de données
AttachDBFilename
Instances utilisateur
Ouverture d'une connexion d'instance utilisateur
SSEUtil
Configuration désactivée par défaut
Connexion à des instances utilisateur d'autres clients
Limitations des instances utilisateur
Problèmes courants
Distribution et déploiement
RANU
Conversion d'une base de données d'instances utilisateur
Conclusion

Introduction

Un des objectifs de conception de la nouvelle version Express Edition de SQL Server 2005 était d'implémenter une intégration beaucoup plus étroite avec les fonctionnalités de conception de base de données de Visual Studio. Le système de projet Visual Studio est très efficace pour la gestion de l'ensemble de fichiers qui constitue le projet. Pour s'intégrer facilement à cet ensemble d'outils, les bases de données SQL Server doivent être manipulées en tant que fichiers. Heureusement, une base de données SQL Server est un ensemble de fichiers, de sorte que la gestion de l'ensemble de fichiers dans le cadre d'un projet Visual Studio n'est pas compliquée. En revanche, la connexion à ces fichiers en tant que base de données à partir d'une application n'est pas aussi simple et automatique. La fonctionnalité Instance utilisateur facilite cette opération.

Association de fichiers de base de données

La compréhension des instances utilisateur dans Microsoft SQL Server 2005 est plus facile si nous comprenons les problèmes qu'elles étaient destinées à résoudre. Cette section décrit l'association de fichiers de base de données à une instance de base de données SQL Server : il s'agit de l'un des éléments facilités par les instances utilisateur.

Pour les besoins de ce document, il existe deux types de fichiers de base de données SQL Server (il en existe plus, mais dans le cas présent, deux sont suffisants). Il s'agit des fichiers de données (.mdf) et des fichiers journaux (.log).

Le fichier qui contient les données de la base présente l'extension .mdf : par exemple, AccountsReceivable.mdf est un fichier de données. À chaque fichier de données correspond un fichier journal contenant le journal des transactions. Le fichier journal présente l'extension .ldf. Par exemple, le fichier journal de notre base de données serait nommé AccountsReceivable_log.ldf.

Ces deux fichiers sont très étroitement liés. Le fichier de base de données contient des informations sur la version exacte du fichier journal. Si vous restaurez le fichier de données à partir d'une sauvegarde sans restaurer la même version du fichier journal, la base de données ne démarre pas. Lorsque vous manipulez les fichiers de base de données de votre projet, il est important de considérer ces deux fichiers comme un ensemble. Par exemple, si vous revenez à une version antérieure du fichier .mdf, vous devez également revenir à la même version du fichier .ldf.

Avant toute connexion à une base de données SQL Server, le serveur doit connaître les fichiers de base de données. Le serveur ouvre les fichiers, valide la version, s'assure que le fichier journal correspond au fichier de base de données, et effectue toutes les opérations de récupération requises pour synchroniser le fichier de base de données avec le fichier journal. Le processus qui consiste à informer un serveur SQL Server en cours d'exécution sur un fichier de base de données est appelé association de la base de données. Si Catherine possède un fichier de base de données auquel elle doit accéder par l'intermédiaire de SQL Server sur un serveur d'entreprise, elle donne les fichiers .mdf et .ldf à son administrateur de base de données (DBA). Le DBA :

1. Associe les fichiers de base de données au serveur à l'aide d'une commande CREATE DATABASE ... FOR ATTACH

2. Crée un accès pour Catherine sur le serveur.

3. Crée un utilisateur pour l'ouverture de session de Catherine dans la base de données.

4. Accorde à l'utilisateur les autorisations requises par Catherine pour exécuter son application.

Ces efforts sont justifiés s'il s'agit d'une application majeure, mais si Catherine développe des applications sur son propre ordinateur, ce travail peut être excessif. Notez que si Catherine est membre du groupe Administrateurs sur l'ordinateur où s'exécute l'instance SQL Server, les trois dernières étapes ne sont pas nécessaires. Cela tient au fait qu'un administrateur peut toujours ouvrir une session et dispose de droits d'administrateur sur toutes les bases de données associées au serveur.

AttachDBFilename

Heureusement, le code du client SQL Server inclut une option appelée AttachDBFilename, qui supprime la nécessité de demander au DBA d'associer les fichiers de base de données à un serveur avant leur utilisation. Lorsque le mot-clé AttachDBFilename est inclus dans une chaîne de connexion, le fichier spécifié est associé à l'instance SQL Server et le client est connecté à la base de données nouvellement associée. L'argument de l'option AttachDBFilename est le nom du fichier à attacher. Voici un exemple :

AttachDbFilename=|DataDirectory|\Database1.mdf;

DataDirectory| est un raccourci pour le répertoire où se trouve le programme qui ouvre la connexion. Pour associer un fichier dans un répertoire différent, vous devez indiquer le chemin complet vers le fichier. Dans ce cas, le fichier journal est nommé Database1_log.ldf et se trouve dans le même répertoire que le fichier de base de données. Si le fichier de base de données est déjà associé à l'instance SQL Server, la connexion est ouverte avec la base de données existante.Il s'agit d'une option intéressante, car si vous êtes administrateur, vous pouvez associer un fichier de base de données et vous y connecter en spécifiant le nom du fichier dans la chaîne de connexion, dans l'application. De nombreux développeurs sont administrateur sur leur système, de sorte que AttachDBFilename fonctionne bien pour eux. Le problème est que Microsoft recommande vivement de ne PAS être administrateur, car cela permet de réduire les dommages en cas d'infection par un virus. Ce dont nous avons besoin dans ce cas est un moyen d'utiliser AttachDBFilename sans être membre du groupe Administrateurs de Windows. La solution est la fonctionnalité Instance utilisateur.

Instances utilisateur

J'ai mentionné plusieurs fois les instances SQL Server sans définir ce qu'elles étaient. Une instance SQL Server est un programme exécutable SQL Server qui s'exécute sur un serveur. Chaque instance possède un nom, un processus sqlservr.exe en mémoire, une mémoire tampon, ses propres copies des bases de données système, ainsi que son propre ensemble de bases de données utilisateur. Par défaut, SQL Server Express s'installe en tant qu'instance nommée « SQLEXPRESS », par exemple. Vous vous connectez à une instance nommée en spécifiant le nom de l'instance avec le nom du serveur dans la chaîne de connexion. C'est la raison pour laquelle vous spécifiez normalement « .\SQLEXPRESS » comme nom du serveur lors de la connexion à une base de données SQL Server Express locale. Le point désigne le serveur local et \SQLEXPRESS désigne l'instance nommée SQLEXPRESS. Le service SQL Server (sqlservr.exe) s'exécute en tant que service Microsoft Windows et s'exécute dans le contexte de l'utilisateur spécifié en tant que compte de service dans le gestionnaire de services Windows. Pour SQL Server Express, ce compte prend par défaut la valeur « NT AUTHORITY\NETWORK SERVICE », même si un compte différent peut être spécifié lors de l'installation.

SQL Server Express étend le concept d'instances SQL Server en prenant en charge les instances utilisateur. Une instance utilisateur est semblable à une instance normale, mais elle est créée à la demande, alors que les instances normales sont créées lors de la configuration. Le compte de service d'une instance utilisateur est l'utilisateur Windows qui a ouvert la connexion SQL Client à la base de données. Autrement dit, si Catherine ouvre une connexion à un fichier de base de données en spécifiant l'option d'instance utilisateur dans la chaîne de connexion, l'instance utilisateur présente Catherine comme compte de service.

Les instances utilisateur sont créées lorsque l'option Instance utilisateur est définie dans la chaîne de connexion SQL Client. Vous trouverez ci-après une section d'un fichier de configuration Visual Basic qui illustre l'option Instance utilisateur.

<connectionStrings>
     <add name="TestVB1.Settings.Database1ConnectionString" 
          connectionString="Data Source=.\SQLEXPRESS;
          AttachDbFilename=|DataDirectory|\Database1.mdf;
          Integrated Security=True;
          User Instance=True"
          providerName="System.Data.SqlClient" />
</connectionStrings>

Voici différents éléments à noter dans la chaîne de connexion.

• La source de données est .\SQLEXPRESS. Les instances utilisateur sont créées par l'instance SQL Server Express parent, de sorte que la connexion initiale doit spécifier l'instance parent.

• AttachDBFilename est utilisé pour spécifier la base de données à associer à l'instance utilisateur.

• Sécurité intégrée est Vrai. Les instances utilisateur fonctionnent uniquement avec Sécurité intégrée : les utilisateurs SQL Server avec nom d'utilisateur et mot de passe ne fonctionnent pas.

• Le nom du fournisseur est System.Data.SqlClient. L'option Instance utilisateur est valide uniquement sur les chaînes de connexion SqlClient.

Lorsqu'une connexion avec cette chaîne de connexion s'ouvre avec succès, l'application utilisateur est connectée à une instance utilisateur de SQL Server Express qui s'exécute sous l'utilisateur ayant ouvert la connexion. L'utilisateur est connecté à la base de données dans le fichier « database1.mdf ». Si Catherine ouvre cette connexion, l'instance utilisateur s'exécute avec Catherine en tant que compte de service. Étant donné que Catherine est le compte de service de l'instance, Catherine dispose de droits administrateur complets à toutes les bases de données associées à l'instance utilisateur, même si elle n'est pas administrateur Windows. C'est la raison pour laquelle l'option AttachDBFilename fonctionne même si Catherine est un utilisateur normal.

Ouverture d'une connexion d'instance utilisateur

Que se passe-t-il lorsque vous ouvrez une connexion avec l'option Instance utilisateur définie sur Vrai ? Les étapes suivantes décrivent ce qui se passe s'il s'agit de la première fois que l'utilisateur a ouvert une connexion d'instance utilisateur.

• La logique SQLClient ouvre une connexion à l'instance SQL Server Express parent (.\SQLEXPRESS par défaut).

• SQL Server Express détecte que l'option Instance utilisateur est définie et qu'il n'existe pas d'instance utilisateur pour cet utilisateur.

• Les fichiers de base de données système master et msdb sont copiés dans le répertoire de l'utilisateur. Dans le cas de Catherine, le répertoire est :

  C:\Documents and Settings\Sally\Local Settings\Application 
  Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
  

  Ces fichiers sont copiés à partir d'un répertoire de modèle créé lorsque l'instance parent est installée. Lorsque l'instance utilisateur démarre, le fichier tempdb, le fichier journal et les fichiers trace sont écrits dans ce même répertoire utilisateur.

• L'instance parent usurpe l'identité de l'utilisateur Windows qui ouvre la connexion et démarre une copie de sqlservr.exe qui s'exécute sous cet utilisateur. L'emplacement des bases de données système est transmis en tant que paramètre. Le nom de l'instance est généré. Par exemple : 69651E0A-5550-46.

• Une connexion de base de données par canaux nommés est établie pour la nouvelle instance. Le nom est basé sur le nom de l'instance. Par exemple :

  \\.\pipe\69651E0A-5550-46\tsql\query.
  

• Le fichier de base de données spécifié dans le paramètre AttachDBFilename est associé à la nouvelle instance et nommé avec le chemin par défaut du fichier :

  [C:\MYDBPROJECTS\TESTVB1\TESTVB1\DATABASE1.MDF]
  

• Le nom du canal nommé est transféré au client SqlClient qui ouvre la connexion.

• Lorsque SqlClient reçoit le nom de la connexion, il ferme la connexion à l'instance parent. Il ouvre une nouvelle connexion à l'instance utilisateur avec le nom du canal nommé renvoyé.

Une fois que l'interface utilisateur a été créée pour un utilisateur particulier, les bases de données système et le canal nommé sont conservés. Par conséquent, après la première connexion, les connexions suivantes effectuent uniquement les deux dernières étapes.

Le processus sqlservr.exe démarré continue de s'exécuter pendant un certain temps après la fermeture de la connexion à l'instance. Par conséquent, il n'a pas besoin d'être redémarré si une autre connexion est ouverte. La durée de conservation est définie par l'option sp_configure « user instance timeout ». Par défaut, cette durée est définie sur 60 minutes, mais vous pouvez utiliser la commande sp_configure pour changer cela.

SSEUtil

SSEUtil est un outil indispensable pour l'utilisation des instances utilisateur. Il ouvre une instance utilisateur et vous permet d'exécuter des commandes SQL sur l'instance utilisateur. Il peut également détacher une interface utilisateur, de sorte que vous puissiez utiliser les fichiers. De nombreuses autres fonctionnalités sont également disponibles et SSEUtil est amélioré avec chaque nouvelle version. Vous pouvez le télécharger à partir du site Web de l'utilitaire SQL Server Express.

Voici quelques opérations que vous pouvez faire avec SSEUtil :

• Associer et dissocier des bases de données.

• Exécuter des instructions SQL et exécuter des fichiers batch SQL.

• Répertorier les instances enfant et s'y connecter.

• Exécuter la commande Checkpoint et réduire une base de données.

• Signer une base de données.

Configuration désactivée par défaut

Pour des raisons de sécurité, SQL Server 2005 comporte plusieurs fonctionnalités qui sont désactivées par défaut afin de réduire la surface de code vulnérable pour les attaques. Un outil qui vous permet de configurer ces options, appelé SAC (Surface Area Configuration), est installé avec SQL Server Express. La modification de la configuration à l'aide de l'outil SAC change les paramètres de l'instance parent, mais n'affecte pas les instances utilisateur. Cela permet à chaque instance d'activer uniquement les options requises pour les applications de cette instance.

Dans la mesure où l'outil SAC ne configure pas les instances utilisateur, nous devons revenir à SSEUtil pour cette configuration. La seule option généralement requise dans les instances utilisateur est l'option « clr enabled », requise si votre application inclut des procédures stockées CLR, des déclencheurs, des types définis par l'utilisateur, etc. La figure suivante illustre comment activer l'option « clr enabled ».

C:\SSEUtil>sseutil -c
Console mode. Type 'help' for more information.
1> sp_configure 'clr enabled','1'
2> go

Command completed successfully.

1> reconfigure
2> go

Command completed successfully.

Dans un but d'exhaustivité, citons deux autres options qui activent OLE Automation et xp_cmdshell. Je ne recommande pas l'utilisation de ces options. Une procédure stockée CLR effectue généralement ce que font ces procédures, mais d'une manière plus sûre et plus fiable. Si vous avez réellement besoin d'utiliser ces options, voici comment utiliser SSEUtil pour les activer.

C:\SSEUtil>sseutil -c
Console mode. Type 'help' for more information.
1> sp_configure 'show advanced option', '1'
2> go

Command completed successfully.

1> reconfigure
2> go

Command completed successfully.

1> sp_configure 'xp_cmdshell', '1'
2> go

Command completed successfully.

1> reconfigure
2> go

Command completed successfully.

1> sp_configure 'Ole Automation Procedures', '1'
2> go

Command completed successfully.

1> reconfigure
2> go

Command completed successfully.

Connexion à des instances utilisateur d'autres clients

La création d'une instance utilisateur et le démarrage du processus d'instance utilisateur peuvent uniquement être effectués à partir d'une connexion SqlClient dans une application Microsoft .NET Framework. Cependant, une fois l'instance utilisateur en cours d'exécution, tout client pouvant se connecter à un canal nommé peut se connecter à l'instance utilisateur en ouvrant une connexion au canal nommé créé pour l'instance utilisateur. Le nom du canal nommé est disponible dans la vue sys.dm_os_child_instances dans l'instance parent. Utilisez l'instruction suivante.

SELECT owning_principal_name, instance_pipe_name 
FROM sys.dm_os_child_instances

La figure 1 illustre le contenu d'une vue sys.dm_os_child_instances typique.

Une fois que vous connaissez le nom du canal nommé, vous pouvez placer np: devant et l'utiliser dans votre chaîne de connexion. Par exemple, voici un extrait d'une session SQLCMD.exe.

C:\>sqlcmd -S np:\\.\pipe\69651E0A-5550-46\tsql\query
1> use [C:\MYDBPROJECTS\TESTVB1\TESTVB1\DATABASE1.MDF]
2> go
Changed database context to 'C:\MYDBPROJECTS\TESTVB1\TESTVB1\DATABASE1.MDF'.
1> select * from test1
2> go
Col1   Col2       Col3
------ ---------- -----------------------
     1 Frank      2005-02-11 00:00:00.000
    12 Sam        2001-03-21 00:00:00.000

Ce canal nommé est un canal nommé local uniquement, de sorte qu'il n'est pas possible de procéder à l'association à une instance utilisateur à partir d'un client distant. En outre, gardez à l'esprit que l'association directe au canal nommé fonctionne uniquement si une autre application qui utilisait une connexion SqlClient a démarré l'instance au cours de l'heure précédente.

Limitations des instances utilisateur

L'architecture unique des instances utilisateur introduit certaines limitations fonctionnelles :

• Seules les connexions locales sont autorisées.

• La réplication ne fonctionne pas avec les instances utilisateur.

• Les requêtes distribuées ne fonctionnent pas vers les bases de données distantes.

• Les instances utilisateur fonctionnent uniquement dans la version Express Edition de SQL Server 2005.

Problèmes courants

L'architecture des instances utilisateur conduit souvent à une confusion lorsque les bases de données ne se comportent pas de la façon prévue. La plupart de ces problèmes sont liés aux fichiers de base de données associés à l'instance utilisateur, ainsi qu'à la façon dont ils sont traités. Les problèmes plus courants sont détaillés ci-après.

• L'instance utilisateur ne peut pas associer la base de données, car l'utilisateur ne possède pas les autorisations requises. L'instance utilisateur s'exécute dans le contexte de l'utilisateur ayant ouvert la connexion, et non sous le compte de service SQL Server normal. L'utilisateur qui a ouvert la connexion d'instance utilisateur doit disposer d'autorisations en écriture sur les fichiers .mdf et .ldf spécifiés dans l'option AttachDbFilename de la chaîne de connexion. Un problème courant se produit lors de l'utilisation de Visual Web Designer. L'application se connecte à une base de données d'instance utilisateur à partir de l'environnement de développement intégré (IDE) de Visual Studio et ne parvient pas à se connecter lorsque la base de données est ouverte par la page Web. Lorsque la page ASP ouvre la base de données, elle s'exécute généralement en tant que ASPNET. Si ASPNET ne dispose pas d'autorisations en écriture sur les fichiers de base de données, la connexion échoue.
  
  Un autre problème courant est l'ouverture avec succès d'un fichier de base de données lorsque la base de données est associée à l'instance SQL Server Express, mais l'échec lorsque vous tentez de l'ouvrir à partir de l'IDE Visual Studio. Cela peut se produire parce que l'instance SQL Server Express s'exécute en tant que « NT AUTHORITY\NETWORK SERVICE », alors que l'IDE s'exécute sous votre compte. Par conséquent, les autorisations peuvent ne pas fonctionner.

• Une variante de ce problème se produit lorsque l'utilisateur qui ouvre la connexion d'instance utilisateur dispose d'autorisations en lecture sur les fichiers de base de données, mais pas d'autorisations en écriture. Dans ce cas, SQL Server associe la base de données en tant que base de données READ_ONLY. Si vous recevez un message indiquant que la base de données est ouverte en lecture seule, vous devez modifier les autorisations sur le fichier de base de données.

• L'autre problème principal concernant les instances utilisateur se produit parce que SQL Server ouvre les fichiers de base de données avec un accès exclusif. Cela est nécessaire parce que SQL Server gère le verrouillage des données de la base en mémoire. Par conséquent, si plusieurs instances SQL Server ont ouvert le même fichier, une corruption de données est possible. Si deux instances utilisateur différentes utilisent le même fichier de base de données, une instance doit fermer le fichier avant que l'autre instance ne puisse l'ouvrir. Il existe deux moyens courants de fermer des fichiers de base de données, détaillés ci-après.

  • L'option Auto Close est définie pour les bases de données d'instances utilisateur, de sorte que s'il n'existe aucune connexion à une base de données pendant 8 à 10 minutes, la base de données est arrêtée et le fichier est fermé. Cela se produit automatiquement, mais cela peut prendre un certain temps, en particulier si la concentration des connexions est activée pour vos connexions.

  • La dissociation de la base de données de l'instance par appel de sp_detach_db ferme le fichier. Il s'agit de la méthode utilisée par Visual Studio pour s'assurer que le fichier de base de données est fermé lorsque l'IDE bascule entre les instances utilisateur. Par exemple, vous utilisez l'IDE pour concevoir une page Web de données. Vous appuyez sur F5 pour exécuter l'application. L'IDE dissocie la base de données, de sorte que ASP.NET puisse ouvrir les fichiers de base de données. Si vous laissez la base de données associée à l'IDE et que vous tentez d'exécuter la page ASP à partir de votre navigateur, ASP.NET ne peut pas ouvrir la base de données, car le fichier est toujours utilisé par l'IDE.

Distribution et déploiement

L'une des fonctionnalités attractives des instances utilisateur est que vous pouvez distribuer votre application de base de données en incluant les fichiers de base de données sur le support avec le code de l'application. Si les fichiers de base de données sont copiés dans le même répertoire que l'application et que |DataDirectory| est utilisé dans l'option AttachDbFilename de la chaîne de connexion, la base de données fonctionne, peu importe où l'application est copiée, dès lors qu'une installation de SQL Server Express est disponible. Si l'utilisateur ne possède pas encore SQL Server Express, vous pouvez inclure SQL Server Express avec votre application, ou vos utilisateurs peuvent le télécharger à partir du Web. Aucune configuration supplémentaire n'est requise pour prendre en charge les instances utilisateur.

RANU

Chaque fonctionnalité logicielle nécessite un acronyme. L'acronyme logique des instances utilisateur (UI) étant déjà pris, l'instance utilisateur est appelée RANU (Run As Normal User, exécuter en tant qu'utilisateur normal). Le nom officiel est donc la fonctionnalité Instance utilisateur, mais vous entendrez souvent parler de RANU.

Conversion d'une base de données d'instances utilisateur

Vous pouvez convertir votre base de données d'instances utilisateur en une base de données SQL Server normale. Vous pouvez notamment faire cela si vous souhaitez que plusieurs utilisateurs se connectent à la base de données, ou si vous devez procéder à la mise à niveau vers une autre version de SQL Server, par exemple parce que vous rencontrez des limitations en termes de performances ou de taille de la base de données. S'il existe plusieurs moyens de faire cela, le moyen le plus simple consiste à associer les fichiers de base de données à une instance SQL Server normale, en utilisant la commande CREATE DATABASE ... FOR ATTACH. Supprimez ensuite les options AttachDbFilename et Instance utilisateur de la chaîne de connexion. Rappelez-vous que pendant que vous êtes connecté à votre propre instance de SQL Server, vous disposez d'autorisations administrateur complètes sur les bases de données associées à cette instance. Lorsque vous passez à une instance SQL Server normale, chaque utilisateur doit posséder un nom d'ouverture de session et un utilisateur de base de données. L'utilisateur de base de données doit recevoir les autorisations requises pour exécuter l'application.

Conclusion

SQL Server 2005 Express Edition inclut une nouvelle fonctionnalité Instance utilisateur, qui permet à une application d'ouvrir une connexion de base de données si le nom du fichier où sont stockées les données de la base est spécifié. Cela fonctionne même si l'utilisateur qui ouvre la connexion n'est pas administrateur. Cette fonctionnalité simplifie la création et la distribution d'applications de base de données connectées par .NET.