Comment se connecter à une instance SQL Server locale par adresse IP dans SQL Server Management Studio? [dupliquer]
J'écris du code de test requis pour se connecter à une instance SQL Server locale (SQL Server 2017 - Developer Edition) par adresse IP. J'ai l'instance SQL Server en cours d'exécution sur ma machine, mais je ne parviens pas à déterminer le bon IP + Port à utiliser pour la propriété Server de ma chaîne de connexion.
Pour commencer, j'essaie au moins de me connecter manuellement à mon instance de serveur avec SQL Server Management Studio (via l'adresse IP) mais je n'arrive pas à le comprendre.
- Comment trouver l'adresse IP et le port de mon instance SQL Server locale?
- Quel est le format correct que je devrais entrer l'adresse IP et le port dans le champ Nom du serveur de SSMS (et est-ce le même format qu'une chaîne de connexion Propriété Serveur)?
J'ai essayé de localiser l'adresse IP en exécutant le code SQL suivant dessus:
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
Mais les champs local_net_address et local_tcp_port sont NULL et le client_net_address est défini sur <local machine>:
J'ai également essayé d'accéder au gestionnaire de configuration SQL Server, dans la section Configuration réseau SQL Server, et d'activer TCP / IP. Ensuite, j'ai essayé d'utiliser un mélange d'adresses IP4 et IP6 avec le port 1433 (qui est vide par défaut dans les propriétés TCP / IP, mais j'ai essayé de le laisser vide et de le définir sur 1433) et cela n'a pas fonctionné.
J'ai reçu les erreurs suivantes en essayant de me connecter via SSMS pour les différentes combinaisons de l'adresse IP4 avec et sans le port spécifié:
Aucun port spécifié:
Port spécifié avec deux points:
Port spécifié avec virgule:
J'ai également essayé de me connecter via 127.0.0.1 avec les mêmes combinaisons de port 1433 ci-dessus en vain. (Je reçois des erreurs similaires à l'exception des combinaisons suivantes.)
127.0.0.1 sans port:
127.0.0.1 avec port via virgule:
Réponses
Une chose que vous pouvez essayer est de définir Enabled sur "no" pour toutes les adresses IP individuelles, et tout en bas, spécifiez le port souhaité. J'utilise généralement le même port pour toutes les IP, donc je les attrape toutes avec une seule configuration. Si pour rien d'autre, comme mesure de dépannage.
La requête que vous exécutez dans une connexion à votre instance de serveur local reflètera les informations de connexion actuelles.
Contribution
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
Production
+---------------+---------------+-------------+-------------------+----------------+--------------------+
| net_transport | protocol_type | auth_scheme | local_net_address | local_tcp_port | client_net_address |
+---------------+---------------+-------------+-------------------+----------------+--------------------+
| Shared memory | TSQL | NTLM | NULL | NULL | <local machine> |
+---------------+---------------+-------------+-------------------+----------------+--------------------+
Lorsque vous ouvrez la boîte de connexion SSMS et entrez vos informations d'identification, vous vous connectez à l'aide de la connexion de mémoire partagée, qui est essentiellement sans passer par la pile TCP / IP de l'ordinateur.
Déterminez l'adresse IP et le port de votre instance
Exécutez la commande suivante dans une invite de commandes élevée pour récupérer les processus, les exécutables et les ID de processus des processus en cours d'exécution:
Contribution
C:\> netstat -abon
Production
TCP 127.0.0.1:50644 0.0.0.0:0 ABHÖREN 6964
[sqlservr.exe]
Vous pouvez trouver plusieurs entrées pour votre processus sqlservr.exe. Vérifiez que le numéro à la fin est le même (ici: 6964). Il s'agit de l'ID de processus de votre service SQL Server. Ouvrez le Gestionnaire des tâches et vérifiez l'ID de processus du processus sqlservr.exe pour cette instance avec la sortie de la nestat -aboncommande. Vous pouvez avoir plusieurs entrées pour le même ID de processus.
Entrées possibles
...
TCP 127.0.0.1:50644 0.0.0.0:0 ABH™REN 6964
[sqlservr.exe]
TCP [::1]:50644 [::]:0 ABH™REN 6964
[sqlservr.exe]
TCP 0.0.0.0:53481 0.0.0.0:0 ABH™REN 6964
[sqlservr.exe]
En regardant les informations ci-dessus, je peux déterminer qu'une de mes instances SQL Server locales est configurée pour écouter sur IPv4 et IPv6 et en utilisant les ports TCP 50644 et 53481.
Dans votre gestionnaire de configuration SQL Server, si vous faites défiler vers le bas, vous trouverez le port dynamique TCP qui est utilisé par votre instance locale.
Avec ces informations, je peux essayer de me connecter en utilisant la boîte de connexion SSMS et en définissant le bon protocole.
Connexion à l'instance SQL Server avec SSMS
Ouvrez SSMS et entrez l'adresse IP de votre ordinateur portable local (ou l'adresse 169.254.xxx.xxx de la configuration TCP / IP SQL Server) et le port dynamique comme ceci:
Et vous y êtes!
Vérification avec votre script
Contribution
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
Production
+---------------+---------------+-------------+-------------------+----------------+--------------------+
| net_transport | protocol_type | auth_scheme | local_net_address | local_tcp_port | client_net_address |
+---------------+---------------+-------------+-------------------+----------------+--------------------+
| TCP | TSQL | SQL | 161.78.198.140 | -12055 | 161.78.198.140 |
+---------------+---------------+-------------+-------------------+----------------+--------------------+
Explication
Le 127.0.0.1:50644dans ma netstat -abonsortie est l'adresse IPv4 utilisée pour la connexion Admin si la configuration TCP / IP d'une instance locale est configurée pour utiliser des ports dynamiques. Dans mon exemple, 127.0.0.1,50644cela ne fonctionnerait pas.
Il en [::1]:50644va de même pour IPv6.
Le 0.0.0.0:53481est la ligne expliquant que le sqlservr.exeprocessus (32) écoute sur toutes les adresses IP sur le port 53481pour une connexion TCP à l'instance SQL Server.
Alternatives
Regardez le ERRORLOGfichier actuel de votre instance pendant la procédure de démarrage pour déterminer ce qu'il faut utiliser:
...
2020-11-19 15:52:04.29 spid11s A self-generated certificate was successfully loaded for encryption.
2020-11-19 15:52:04.29 spid11s Server is listening on [ 'any' <ipv6> 53481].
2020-11-19 15:52:04.29 spid11s Server is listening on [ 'any' <ipv4> 53481]. <<== HERE!!
2020-11-19 15:52:04.29 spid11s Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2016CI ].
2020-11-19 15:52:04.29 spid11s Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL2016CI\sql\query ].
2020-11-19 15:52:04.30 Server Server is listening on [ ::1 <ipv6> 50644].
2020-11-19 15:52:04.30 Server Server is listening on [ 127.0.0.1 <ipv4> 50644].
2020-11-19 15:52:04.30 Server Dedicated admin connection support was established for listening locally on port 50644. <<== HERE!!
...
Bonne chance.
Mon article de référence Que sont les chaînes de connexion valides pour la boîte de connexion SSMS? sur les configurations multi-instances pour obtenir les paramètres TCP / IP sur un seul ordinateur contient plus d'informations sur ce à quoi s'attendre avec des configurations multi-instances sur un serveur / ordinateur portable.