Meilleur auteur de réponses
connection distante au SQL Serveur 2008

Question
-
Bonjour,
depuis quleques jours je suis bloqué devant cette situation: j'ai un sql serveur 2008 Developer edition installé sur une machine windows seven,
alors que tous fonctionne comme il faut localement, je suis incapable a me connecté au serveur depuis un autre ordinateur dans le méme réseau,
etant donné que :
SQL Serveuur est configurer pour accepter REMOTE CONNECTIOnS
TCP/IP est activer
Pare-feu et configurer pour laissé passé les packet vers le port 1433
pare feu et configurer pour le sqlbrowser.exe
routeur et configurer pour transferer les requete vers le serveur
je sais pas quoi autre je peut ajouté.
TITLE: Connect to Server
——————————
Cannot connect to Database Server.
——————————
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)
Réponses
-
Cannot connect to 192.168.1.27/NXT2.
Vous utilisez bien le backslash et pas le slash ?
192.168.1.27\NXT2
Cordialement
Fred
- Marqué comme réponse Mohamed.alami lundi 21 mai 2012 10:20
Toutes les réponses
-
Bonjour,
utilisez-vous l'instance par défaut ou une instance nommée ?
Si c'est une instance nommée, le port TCP/IP est-il fixé ou dynamique ?
SQLBrowser est il démarré ?
Avez-vous fait un test TELNET à partir d'un autre poste pour vérifier que le serveur est joignable sur le port adéquat ? (vérifier aussi sur le serveur que le port est à l'écoute netstat -na)
Cordialement
Fred
-
Bonjour,
merci pour votre réponse, mais tous ce que vous avez mentionné fontionne bien,
c'est une instance nommé, le port TCP/IP et modifier vers port fixé
SQLBrowser est démarré
quand je Telnet depuis un autre ordinateur, cmd m'affiche une écran noire, mais pas d'érreur j'assume que le Telnet fonctionne.
j'ai verifer que le serveur est a lecoute sur le port 1433
j'ai meme désactiver le pare-feu complètement.
Merci
- Modifié Mohamed.alami mercredi 16 mai 2012 10:30
-
-
Bonjour,
Pouvez vous nous faire suivre le fichier errorlog de SQL, cela nous donnera peut être une piste.
Comme est configurée la sécurité de votre SQL ? Mixe ou windows seulement ? Quel compte utilisez vous pour vous conencter à distance ? Un compte SQL ou bien un compte windows ?
En local vous passez en shared memory et il y a de fortes chance que vous ayez configuré lors de l'installation un compte local en tant que sysadmin de votre instance SQL. Si le second PC n'est pas dans le même domaine, si le compte utilisateur que vous employez n'est pas le même, vous vous prenez aussi un une erreur de connexion.
C'est pour cela que le fichier errorlog est imporant, il devrait nous donner uen partie de la réponse.
Cdlt,
Christophe
Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM
-
Bonjour,
je peut me connecte en local avec les deux method ip/instance ou bien ordinateur/instance
le serveur est configurer pour authetification mixte, et j'utilise un compte sql server pour me connecté,
je travail pas dans un domaine mais dans un groupe de travail (je pense pas que c'est ca le probleme..!)
Cannot connect to 192.168.1.27/NXT1.
===================================
Une erreur liée au réseau ou spécifique à l'instance s'est produite lors de l'établissement d'une connexion à SQL Server. Le serveur est introuvable ou n'est pas accessible. Vérifiez que le nom de l'instance est correct et que SQL Server est configuré pour autoriser les connexions distantes. (provider: Fournisseur de canaux nommés, error: 40 - Impossible d'ouvrir une connexion à SQL Server) (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=1326&LinkId=20476
------------------------------
Error Number: 1326
Severity: 20
State: 0
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
Merci pour votre aide
-
Le fichier errorlog SQL s'il vous plait.
Ceci dit, le message d'erreur comporte une partie de l'info :
provider: Fournisseur de canaux nommés, error: 40 - Impossible d'ouvrir une connexion
et pas TCP IP !!!
Forcez TCP sur le client pour voir.
Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM
-
Bonjour,
je peut pas voire grand chose dans le error log, le voila en tous cas ésperant que tu verra plus d'info.
merci infiniment pour votre aide
--------------------------------------------------------------------------------------------------------------------------------
2012-05-17 09:26:04.17 Server Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Express Edition on Windows NT 6.1 <X86> (Build 7600: )
2012-05-17 09:26:09.21 Server (c) 2005 Microsoft Corporation.
2012-05-17 09:26:09.21 Server All rights reserved.
2012-05-17 09:26:09.21 Server Server process ID is 3084.
2012-05-17 09:26:09.21 Server System Manufacturer: 'SAMSUNG ELECTRONICS CO., LTD.', System Model: 'RC408/RC508/RC708'.
2012-05-17 09:26:09.21 Server Authentication mode is MIXED.
2012-05-17 09:26:09.21 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
2012-05-17 09:26:09.21 Server This instance of SQL Server last reported using a process ID of 3080 at 16/05/2012 18:44:44 (local) 16/05/2012 17:44:44 (UTC). This is an informational message only; no user action is required.
2012-05-17 09:26:09.21 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
2012-05-17 09:26:09.83 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2012-05-17 09:26:09.83 Server Detected 4 CPUs. This is an informational message; no user action is required.
2012-05-17 09:26:10.13 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2012-05-17 09:26:10.18 Server Node configuration: node 0: CPU mask: 0x0000000f Active CPU mask: 0x0000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2012-05-17 09:26:10.22 spid6s Starting up database 'master'.
2012-05-17 09:26:10.27 spid6s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2012-05-17 09:26:10.37 spid6s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2012-05-17 09:26:10.44 spid6s SQL Trace ID 1 was started by login "sa".
2012-05-17 09:26:10.44 spid6s Starting up database 'mssqlsystemresource'.
2012-05-17 09:26:10.45 spid6s The resource database build version is 10.00.2531. This is an informational message only. No user action is required.
2012-05-17 09:26:10.55 spid6s Server name is 'SAMSUNG-PC\SQLEXPRESS'. This is an informational message only. No user action is required.
2012-05-17 09:26:10.55 spid10s Starting up database 'model'.
2012-05-17 09:26:10.56 spid6s Informational: No full-text supported languages found.
2012-05-17 09:26:10.56 spid6s Starting up database 'msdb'.
2012-05-17 09:26:10.72 Server A self-generated certificate was successfully loaded for encryption.
2012-05-17 09:26:10.72 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2012-05-17 09:26:10.72 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2012-05-17 09:26:10.72 Server Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2012-05-17 09:26:10.72 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2012-05-17 09:26:10.72 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2012-05-17 09:26:26.20 spid10s Recovery completed for database model (database ID 3) in 9 second(s) (analysis 19 ms, redo 0 ms, undo 7445 ms.) This is an informational message only. No user action is required.
2012-05-17 09:26:38.60 spid10s Clearing tempdb database.
2012-05-17 09:26:47.04 spid10s Starting up database 'tempdb'.
2012-05-17 09:26:47.13 spid13s The Service Broker protocol transport is disabled or not configured.
2012-05-17 09:26:47.13 spid13s The Database Mirroring protocol transport is disabled or not configured.
2012-05-17 09:26:47.24 spid13s Service Broker manager has started.
2012-05-17 09:26:47.25 spid6s Recovery is complete. This is an informational message only. No user action is required.
2012-05-17 09:57:29.95 Server Server resumed execution after being idle 130 seconds: user activity awakened the server. This is an informational message only. No user action is required.
-
Vous n'avez pas activé TCP/IP au niveau des protocoles réseau. Seuls les NP écoutent les rq clientes :
2012-05-17 09:26:10.72 Server Server local connection provider is ready to accept connection on [\\.\pipe\SQLLocal\SQLEXPRESS ].
2012-05-17 09:26:10.72 Server Server local connection provider is ready to accept connection on [\\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2012-05-17 09:26:10.72 Server Dedicated administrator
Activez TCP et restartez le service.
Cdlt
Christophe
Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM
-
Hi Christophe,
J'ai activer TCP/IP sur le panneau de configuration d'SQL pour SQLEXPRESS instance, j'ai aussi démarrer SQLBrowser sur la machine cliente, mais ça retourne le même erreur.
Merci
2012-05-17 09:26:10.72 Server Server local connection provider is ready to accept connection on [\\.\pipe\SQLLocal\SQLEXPRESS ].
2012-05-17 09:26:10.72 Server Server local connection provider is ready to accept connection on [\\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2012-05-17 09:26:10.72 Server Dedicated administrator
This is the log on my local machine, i could connect by ip and everything looks ok:
05/21/2012 08:46:14,spid53,Unknown,Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
05/21/2012 08:46:12,spid53,Unknown,Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
05/21/2012 08:29:22,spid13s,Unknown,A new instance of the full-text filter daemon host process has been successfully started.
05/21/2012 08:27:27,spid6s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
05/21/2012 08:27:10,spid6s,Unknown,Recovery is writing a checkpoint in database 'Tfs_Configuration' (7). This is an informational message only. No user action is required.
05/21/2012 08:27:06,spid23s,Unknown,Starting up database 'NXTDataBase'.
05/21/2012 08:27:06,spid20s,Unknown,Starting up database 'Tfs_Configuration'.
05/21/2012 08:27:06,spid19s,Unknown,Starting up database 'ReportServer$NXT1TempDB'.
05/21/2012 08:27:06,spid22s,Unknown,Starting up database 'Tfs_DefaultCollection'.
05/21/2012 08:27:06,spid18s,Unknown,Starting up database 'ReportServer$NXT1'.
05/21/2012 08:27:06,spid17s,Unknown,Starting up database 'msdb'.
05/21/2012 08:26:41,spid14s,Unknown,Service Broker manager has started.
05/21/2012 08:26:39,spid14s,Unknown,The Database Mirroring protocol transport is disabled or not configured.
05/21/2012 08:26:39,spid14s,Unknown,The Service Broker protocol transport is disabled or not configured.
05/21/2012 08:26:37,spid11s,Unknown,Starting up database 'tempdb'.
05/21/2012 08:26:34,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
05/21/2012 08:26:34,Server,Unknown,The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b<c/> state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
05/21/2012 08:26:33,Server,Unknown,Dedicated admin connection support was established for listening locally on port 55747.
05/21/2012 08:26:33,Server,Unknown,Server is listening on [ 127.0.0.1 <ipv4> 55747].
05/21/2012 08:26:33,Server,Unknown,Server is listening on [ ::1 <ipv6> 55747].
05/21/2012 08:26:33,Server,Unknown,Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$NXT1\sql\query ].
05/21/2012 08:26:33,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\NXT1 ].
05/21/2012 08:26:33,Server,Unknown,A self-generated certificate was successfully loaded for encryption.
05/21/2012 08:26:30,spid11s,Unknown,Clearing tempdb database.
05/21/2012 08:26:28,spid6s,Unknown,Server name is 'ACER-PC\NXT1'. This is an informational message only. No user action is required.
05/21/2012 08:26:28,spid11s,Unknown,Starting up database 'model'.
05/21/2012 08:26:26,spid6s,Unknown,The resource database build version is 10.50.1617. This is an informational message only. No user action is required.
05/21/2012 08:26:26,spid6s,Unknown,Starting up database 'mssqlsystemresource'.
05/21/2012 08:26:26,spid6s,Unknown,SQL Trace ID 1 was started by login "sa".
05/21/2012 08:26:24,spid6s,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'NXT1'.
05/21/2012 08:26:24,spid6s,Unknown,SQL Server Audit has started the audits. This is an informational message. No user action is required.
05/21/2012 08:26:24,spid6s,Unknown,SQL Server Audit is starting the audits. This is an informational message. No user action is required.
05/21/2012 08:26:24,spid6s,Unknown,Resource governor reconfiguration succeeded.
05/21/2012 08:26:19,spid6s,Unknown,Recovery completed for database master (database ID 1) in 1 second(s) (analysis 416 ms<c/> redo 160 ms<c/> undo 376 ms.) This is an informational message only. No user action is required.
05/21/2012 08:26:19,spid6s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
05/21/2012 08:26:19,spid6s,Unknown,0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
05/21/2012 08:26:18,spid6s,Unknown,2 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
05/21/2012 08:26:15,spid6s,Unknown,Starting up database 'master'.
05/21/2012 08:26:13,Server,Unknown,Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
05/21/2012 08:26:04,Server,Unknown,Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
05/21/2012 08:26:00,Server,Unknown,Detected 4 CPUs. This is an informational message; no user action is required.
05/21/2012 08:26:00,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
05/21/2012 08:25:59,Server,Unknown,Registry startup parameters: <nl/> -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.NXT1\MSSQL\DATA\master.mdf<nl/> -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.NXT1\MSSQL\Log\ERRORLOG<nl/> -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.NXT1\MSSQL\DATA\mastlog.ldf
05/21/2012 08:25:59,Server,Unknown,This instance of SQL Server last reported using a process ID of 3172 at 18/05/2012 18:18:21 (local) 18/05/2012 18:18:21 (UTC). This is an informational message only; no user action is required.
05/21/2012 08:25:59,Server,Unknown,Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.NXT1\MSSQL\Log\ERRORLOG'.
05/21/2012 08:25:59,Server,Unknown,Authentication mode is MIXED.
05/21/2012 08:25:59,Server,Unknown,System Manufacturer: 'Acer'<c/> System Model: 'Aspire 5742'.
05/21/2012 08:25:59,Server,Unknown,Server process ID is 1508.
05/21/2012 08:25:59,Server,Unknown,All rights reserved.
05/21/2012 08:25:59,Server,Unknown,(c) Microsoft Corporation.
05/21/2012 08:25:58,Server,Unknown,Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) <nl/> Apr 22 2011 19:23:43 <nl/> Copyright (c) Microsoft Corporation<nl/> Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
- Modifié Mohamed.alami lundi 21 mai 2012 08:56
-
Bonjour,
vos postes sont ils en XP SP2 ?
Si oui, regardez ici : http://support.microsoft.com/kb/839269/fr
Si non, essayez en désactivant le named pipes et regardez si vous pouvez vous connecter au serveur, d'abord localement, puis via le réseau.
Cordialement
Fred
-
Bonjour FDLD,
les deux postes sont en Win 7,
j'ai désactiver les Named Pipes, ca marche toujours en local, je peut me connecté a travers l’adresse ip de mon ordinateur, ou bien le nom.
mais dans le client distance ca ne marche pas encore, le même erreur.
TITLE: Connect to Server
------------------------------
Cannot connect to 192.168.1.27/NXT2.
------------------------------
ADDITIONAL INFORMATION:
Une erreur liée au réseau ou spécifique à l'instance s'est produite lors de l'établissement d'une connexion à SQL Server. Le serveur est introuvable ou n'est pas accessible. Vérifiez que le nom de l'instance est correct et que SQL Server est configuré pour autoriser les connexions distantes. (provider: Fournisseur de canaux nommés, error: 40 - Impossible d'ouvrir une connexion à SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476Cordialement.
-
Cannot connect to 192.168.1.27/NXT2.
Vous utilisez bien le backslash et pas le slash ?
192.168.1.27\NXT2
Cordialement
Fred
- Marqué comme réponse Mohamed.alami lundi 21 mai 2012 10:20
-