none
connection distante au SQL Serveur 2008 RRS feed

  • 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)

    mercredi 16 mai 2012 09:26

Réponses

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

    mercredi 16 mai 2012 09:34
  • 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


    mercredi 16 mai 2012 10:29
  • Bon,

    et en mettant le nom du serveur virgule le port dans SSMS ?

    Ou l'IP ?

    Cordialement

    mercredi 16 mai 2012 10:46
  • 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

    mercredi 16 mai 2012 12:22
  • 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

    mercredi 16 mai 2012 13:30
  • 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

    mercredi 16 mai 2012 15:08
  • 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.

    jeudi 17 mai 2012 09:15
  • 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

    jeudi 17 mai 2012 19:35
  • 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:

    Date,Source,Severity,Message
    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)


    lundi 21 mai 2012 08:53
  • 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

    lundi 21 mai 2012 09:08
  • 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=20476

    Cordialement.

    lundi 21 mai 2012 09:41
  • Cannot connect to 192.168.1.27/NXT2.

    Vous utilisez bien le backslash et pas le slash ?

    192.168.1.27\NXT2

    Cordialement

    Fred

    lundi 21 mai 2012 09:53
  • Merci FDLD,

    c'était ca l’erreur, j'ai même pas imaginer...merci beaucoup, ça marche maintenant.

    cordialement

    Mohamed

    lundi 21 mai 2012 10:20