Autoriser les accès distants
L’accès distant se configure par un des outils livré dans toutes les éditions de SQL Server 2005 « SQL Server Surface Area Configuration » ou « Configuration de la surface d'exposition SQL Server ».
Cet outil permet de configurer les accès aux services de SQL Server, ainsi que les fonctionnalités qui y sont disponibles. La plupart des fonctionnalités sensibles sont désactivées (tel que xp_cmdshell) et les connexions distantes ne sont pas autorisées (par défaut) sur les éditions Express et Developer.
L’outil se trouve dans le menu « Démarrer » / « Tous les programmes » / « Microsoft SQL Server 2005 » / « Outils de Configuration » ou « Configuration Tools ».
Pour tout ce qui est gestion des services et des connexions allez dans « Configuration de la surface d'exposition pour les services et les connexions » ou « Surface Area Configuration for Services and Connections »
Cliquez sur le nom de l’instance qui vous intéresse (ici MSSQLSERVER qui est le nom de l’instance par défaut, si vous êtes sous SQL Server Express ce sera plus vraisemblablement SQLEXPRESS), puis « Database Engine » / « Remote Connexions » puis sur la partie droite « Local and remote connections » / « Using TCP/IP only » (où une des autres options suivant les cas) ou en français « Moteur de base de données » / « Connexions distantes » et à droite « Connexions locales et distantes » / « Connexion TCP/IP uniquement ».
Cliquez sur OK, et fermez le programme, votre instance de SQL Server est désormais accessible à distance.
Instances nommée avec port dynamique & SQL Browser
« SQL Browser » est un service dont le rôle est d’écouter sur le port 1434 en UDP, c’est le port où anciennement SQL Server 2000 répondait aux questions du type : « A quel port est joignable l’instance XXXX ? ».
Ce n’est plus le service de base de données qui est chargé de cela, pour plusieurs raisons, l’une d’entre elle étant que plusieurs services peuvent supporter la notion d’instance nommée (Analysis Services, etc.) sous SQL Server 2005.
Mais pourquoi est ce nécessaire ? Tout simplement parce qu’en TCP/IP seule l’instance par défaut a un port fixe (1433) toutes les autres instances fonctionne en allocation de port dynamique. Ce qui fait qu’une instance nommée a de fortes chances d’obtenir un nouveau port TCP a chaque démarrage de son service. Le SQL Brower permet au client de demander le port de l’instance qui porte tel ou tel nom et ainsi de pouvoir s’y connecter directement.
Dans ce genre de situation le service SQL Browser doit être démarré en permanence. Pour cela allez dans « Démarrer » / « Tous les programmes » / « Microsoft SQL Server 2005 » / « Outils de Configuration » ou « Configuration Tools ».
Ouvrez « SQL Server Configuration Manager » ou « Gestionnaire de Configuration SQL Server ». Puis assurez-vous que le service « SQL Server Browser » est bien démarré.
Pour faire en sorte que ce service démarre à chaque démarrage de Windows cliquez sur celui-ci avec le bouton droit, puis sélectionnez « Propriétés » ou « Properties ».
Allez dans l’onglet « Service » puis au niveau de « Start Mode » ou « Mode de démarrage » sélectionnez « Automatic » ou « Automatique »
Cliquez ensuite sur OK. Pour démarrer le service faites bouton droit sur celui-ci puis choisissez « Démarrer » ou « Start ».
Instances nommées avec port fixe
Il est possible de configurer une ou plusieurs instances de SQL Server pour qu’elle utilise un port TCP fixe. L’instance par défaut, elle, utilisera toujours le port 1433 par défaut et sera donc en configuration de port fixe. Il est cependant possible de changer ce numéro de port.
Pour configurer cela, allez dans « Démarrer » / « Tous les programmes » / « Microsoft SQL Server 2005 » / « Outils de Configuration » ou « Configuration Tools », puis ouvrez « SQL Server Configuration Manager » ou « Gestionnaire de Configuration SQL Server ».
Dans « SQL Server 2005 Configuration réseau » ou « SQL Server 2005 Network Configuration » (dans mon cas je suis sur un système 64 bits, c’est pour cette raison que la partie configuration 32bits est marquée (32bits)).
Choisissez l’instance à configurer, puis double cliquez sur TCP/IP.
Cliquez sur l’onglet « IP Addresses » ou « Adresses IP ».
Dans la section « IP All » ou « Toutes IP » (à mon que vous ne souhaitiez cette configuration que pour une adresse), renseignez le numéro de port que vous souhaitez utiliser, et enlevez le 0 de l’allocation de port dynamique.
Le numéro de port TCP est compris en 1 et 65535, cependant n’utilisez ni des valeurs trop hautes (utiliser pour les translations d’adresse, et les communications avec les serveurs), ni trop basses (allouées à des services existant présent ou non sur votre machine). Le plus simple, vérifiez avec netstat si le port est actuellement utilisé :
netstat -A
Il vous renverra la liste de tous les ports actuellement à l’écoute sur votre machine, vous pourrez en déduire le port à utiliser pour votre serveur.
Cliquez sur OK, votre serveur est dorénavant accessible via un port TCP fixe.
Côté client - Accès par fichier
Pour accéder à un serveur SQL, il y a plusieurs solution, SQL Server 2005 en ajoute une de plus : l’accès par fichier(s).
En fait SQL Server 2005 est un serveur de base de données, mais pour éviter le fait que la base de données soir déjà être installée sur une machine pour pouvoir l’utiliser, Microsoft à ajouté la possibilité d’accéder à une base de données SQL Server en référençant le/les fichier(s) de base de données. Ceci pour faciliter les déploiements avec utilisation de base de données en local.
On voit bien ici que l’on référence un fichier, qui est le fichier principal de base de données (extension MDF). La chaîne de connexion correspondante est la suivante :
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True
Deux choses sont intéressantes, on référence le fichier et le serveur. Quand on se connecte de cette manière il est nécessaire d’avoir un serveur SQL Server 2005 s’exécutant sur la même machine, sinon il est impossible de se connecter à cette base de données. Autre impératif, il vous faut tous les fichiers de la base de données (.MDF, .NDF et .LDF), même si vous pointez que sur le premier.
Que ce passe t il au niveau du serveur lorsqu’une telle connexion se produit… Et bien le serveur attache cette base de données, et ajoute celle-ci à la liste de ses bases de données. Lors de la déconnexion on fait l’inverse et on libère les fichiers de base de données.
Cela reviendrait à exécuter la commande suivante sur le serveur de base de données :
CREATE DATABASE AdventureWorks ON (FILENAME =‘ C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf’) FOR ATTACH'
Comme ce type d’accès n’est que local, si vous souhaitez permettre un accès distant à votre base de données, exécutez la commande ci dessus sur le serveur SQL, ce qui va avoir pour effet d’attacher « définitivement » votre base de données au serveur (donnez un nom correct à votre base de données pour la retrouver par la suite). Vérifiez bien aussi que vous avez accès à cette base de données avec un compte (soit que vous créez avec CREATE LOGIN, soit un compte Windows ou SQL existant).
Côté client - Accès à un serveur distant
Fabrice a déjà fait une présentation de la partie cliente de SQL Server 2005, vous pourrez la lire ici : http://www.asp-php.net/tutorial/sql-server/sqlncli.php
Pour la création de la connexion par interface graphique vous passez par ici
La chaîne de connexion est maintenant la suivante :
Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True
Ici j’ai toujours à faire à une connexion locale (le « . » fait référence à ma machine), et à une instance sur celle-ci.
Comment se connecter à une machine distante :
- MaMachine
- MaMachine\MonInstance
- MaMachine,Port
- Adresse
- Adresse\MonInstance
- Adresse,Port
- Alias
Toutes ces 7 variantes fonctionnent, soit vous passez le nom de la machine, soit son adresse IP (en connexion TCP/IP). Si vous ne spécifiez ni le nom de l’instance, ni le numéro de port, on présume que vous vous adressez à l’instance par défaut.
Vous ne pouvez utiliser la forme \MonInstance que si le service SQL Browser tourne, sinon vous êtes obligé de passer par le numéro de Port de l’instance.
Pour faciliter les choses il est possible d’enregistrer un Alias, qui permet de masquer les détails de connexion. Du coup seul le nom de l’Alias est à fournir dans la chaîne de connexion sans plus de détails.
Pour créer ce type d’alias, allez dans l’outil de configuration client de votre machine. Il s’exécute via :
cliconfg
Pour plus de détail sur cette commande : http://blogs.codes-sources.com/christian/archive/2007/01/08/sql-server-configuration-du-client-sur-toutes-machines.aspx
Dans le second onglet vous pouvez créer un alias vers l’instance SQL Server de votre choix.
On retrouve ici toutes les informations à renseigner pour la création d’une Alias.
Cet Alias est bien entendu à créer sur le poste client, et sur tous les postes client en ayant besoin.