none
Unable to Add New Query in TableAdapter Query Configuration Wizard

    Question

  • I am having real problems all of a sudden changing the TableAdapters in one of my ADO.NET projects in Visual Studio 2010 Professional

    The Connection string to my remote MSSQL Server Database is fine, and tests OK.

    Existing Queries can be executed OK, and will return preview data without any problems, and the application behaves normally in Debug mode.

    But I cannot add new parameterized Queries to my TableAdapters in this project in the Designer as follows:

    Table Adapter > Add > Query

    Use SQL Statements

    SELECT which returns rows.

    I then enter the new query with the parameter e.g. WHERE (jobid = @jobid)

    Click Next. The Wizard then hangs for 30 seconds or so and eventually times out with the error:

    "An unexpected error has occurred.

    Error Message: Failed to Open a connection to the database

    Check the connection and network state and try again"

    I can query this db in SSMS from the same machine

    Disabled firewall on both side, no effect

    Tried the solution on a completely different machine, still no effect.

    Tearing my hair out with this and don't really know where to turn. If the Connection String works, and the existing queries work, why can't I add a New Query? Does the Wizard need to do extra work via a different method somewhere along the line?

    Please help!



    Friday, July 27, 2012 4:08 PM

Answers

  • I have sussed it - it was a bad synonym inside the database schema. The SChannel errors are totally misleading.

    We had a SQL Server which we replaced on one of our satellite depot sites around 6 months ago. A couple of weeks back we powered off the old machine for recovery back to head office.

    The Linked Server object on the SQL Server that was giving me the issue was still in place, as were the synonyms that were dependent upon it. As the initial error suggested that Visual Studio, and I quote "could not open a connection to the database", I did not even think initially about looking at what was going on query-wise on the server. My initial investigations centred around why I could not connect. First port of call was therefore the Event Viewer where I encountered the similarly bogus SChannel error, backed up by my belief that the SQL Server had never required encryption.

    It was only by determining that we could successfully configure DataSets against other databases on the same server did I get suspicious and decide to start looking at what was going on after the initial connection was made. The first tool here was the Activity Monitor which showed a VS process with a wait resource of External ExternalResource=MSSQL_DQ. 

    The Query being run was a schema query against the database schema, as VS attempted to validate what I was requesting in the TableAdapter query against the schema for the database. Part of this query was to list all the synonyms in the system. I still didn't twig on at this point that this might be connected to us retiring that box.

    I used the query listed at http://sqlserverpedia.com/wiki/Misc_DMV_queries to gain a good idea of what was going on transaction wise and to list the wait resource in a friendly fashion at the point of the execution of the VS task. This listed the netbios name of our dead SQL box.

    I then removed the linked server object, dropped the synonyms that depended on it and now the operation completes in Visual Studio without any errors (and without a huge timeout wait). I would never have got there on the error messages alone as they are quite generic.
    • Marked as answer by Blueboy1894 Tuesday, July 31, 2012 1:06 PM
    Tuesday, July 31, 2012 12:53 PM

All replies

  • Update:

    When this happens I get two Schannel errors in the System Event Log - 36882 and 36888.

    This suggests it is trying to use SSL to connect to the SQL Server and that the certificate being exchanged is not trusted.

    The connection string has Encrypt set to False and the SQL Server Configuration also has "Require Encryption" set to false.

    The certificate text has "SELF_ASSIGNED_FALLBACK" in it which suggests it is a Self Assigned Cert which is not part of the Trusted Root Authority. Where this is being picked up from I do not know however.

    Very stuck. Any ideas appreciated.

    Monday, July 30, 2012 9:40 AM
  • Can you show the details in the event log errors that might be helpful to solve this problem? Do you know is SQL Server configured for SSL connection that might be where certificate was created?
    Monday, July 30, 2012 10:41 AM
  • Hi Sam and thanks for answering. I really appreciate it.

    The SQL Server Configuration on the network connections is set to Require Encryption = False. To the best of my knowledge it has never been True.

    The detail of the Schannel errors are:

    36888: The certificate received from the remote server was issued by an untrusted certificate authority. Because of this, none of the data contained in the certificate can be validated. The SSL connection request has failed. The attached data contains the server certificate.

    36882: The following fatal alert was generated: 48. The internal error state is 552.

    I can't understand why, if both client and server do not require encryption, why I am getting SChannel errors.

    Let me know if there is any more info you need.

    Monday, July 30, 2012 11:33 AM
  • If you are sure that certificate is not configured on SQL server, then could it be that certificate is part of the Windows server where SQL server is? Have you or anybody else done something with the certificates?
    Monday, July 30, 2012 1:29 PM
  • Not that I am aware of, no.

    I cannot see anything on that box that is not part of the standard certificate setup for the domain.

    The fact that it comes up as self_assigned_fallback in the Event makes it seem to me that it is something SQL related though.

    Monday, July 30, 2012 2:33 PM
  • I think so too, but if you are sure about the SQL server configuration then that seems odd. Have you read the following article that seems to be relating to similar issue http://support.microsoft.com/kb/2007728. Also if you still think that this might be SQL server problem, then you should post the question to the SQL server forums at http://social.technet.microsoft.com/Forums/en-US/category/sqlserver.

    Monday, July 30, 2012 5:11 PM
  • Yep, been there, the Force Encryption is set to false and there is no certificate configured on the box.

    The only thing I have read which suggests it may not be to do with this is that SQL Server encrypts the login traffic (not the data traffic), regardless of what you do in the Configuration Manager. If, for whatever reason, it cannot find a trusted CA at the time, then it will use a self-signed certificate to do so.

    If I knew how to get at that and export it, I might have more luck.

    Perhaps that is a question for the SQL Forum as discussed.

    Monday, July 30, 2012 5:33 PM
  • I have sussed it - it was a bad synonym inside the database schema. The SChannel errors are totally misleading.

    We had a SQL Server which we replaced on one of our satellite depot sites around 6 months ago. A couple of weeks back we powered off the old machine for recovery back to head office.

    The Linked Server object on the SQL Server that was giving me the issue was still in place, as were the synonyms that were dependent upon it. As the initial error suggested that Visual Studio, and I quote "could not open a connection to the database", I did not even think initially about looking at what was going on query-wise on the server. My initial investigations centred around why I could not connect. First port of call was therefore the Event Viewer where I encountered the similarly bogus SChannel error, backed up by my belief that the SQL Server had never required encryption.

    It was only by determining that we could successfully configure DataSets against other databases on the same server did I get suspicious and decide to start looking at what was going on after the initial connection was made. The first tool here was the Activity Monitor which showed a VS process with a wait resource of External ExternalResource=MSSQL_DQ. 

    The Query being run was a schema query against the database schema, as VS attempted to validate what I was requesting in the TableAdapter query against the schema for the database. Part of this query was to list all the synonyms in the system. I still didn't twig on at this point that this might be connected to us retiring that box.

    I used the query listed at http://sqlserverpedia.com/wiki/Misc_DMV_queries to gain a good idea of what was going on transaction wise and to list the wait resource in a friendly fashion at the point of the execution of the VS task. This listed the netbios name of our dead SQL box.

    I then removed the linked server object, dropped the synonyms that depended on it and now the operation completes in Visual Studio without any errors (and without a huge timeout wait). I would never have got there on the error messages alone as they are quite generic.
    • Marked as answer by Blueboy1894 Tuesday, July 31, 2012 1:06 PM
    Tuesday, July 31, 2012 12:53 PM
  • Thank you very much, had same issue. Thanks to you only wasted 2 hours. 
    Wednesday, August 9, 2017 7:58 PM