5

I am trying to create the application in this demo but with my own database. All I am trying to do for now, is show one data table. I am using Visual studio 2017 pro, and am connecting to an older SQL Server version 10.50.1600. I am able to connect to the database with windows authentication via SSMS, but when I try to connect with my application I get the following error message.

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The operation completed successfully)

  • I have the following capabilities enabled: Enterprise Authentication, Internet (Client & Server), Internet(Client), Private Networks(Client & Server)
  • I changed the server to another, more current DB server, and it worked... so I am thinking it may be a setting on the database server that I have to change. I have no idea how to know what to change though.
  • I am running under UWP build 16299, and have followed the steps in the demo very closely. I've re-created this app a few times to ensure that I am not missing any steps. The rest of the app works fine.

I have spent hours looking around the internet for a solution for this error. There seem to be many reasons that this error can come up but I have yet to see a solution that worked for me.

I'm wondering if people can give me tips on where I can look to see why this is happening. I can't see anything helpful in the Autos list, but I'll attach it in case there's something that jumps out at others.

My connection string and class are below. As I mentioned, it does connect, but then complains about something at login:

Screenshot of error message

enter image description here Connection String 

public string ConnectionString { get; set; } = "Server=servername; Trusted_Connection=Yes; Integrated Security=True;";

Connection Class

public ObservableCollection<MyAssignments> GetMyAssignments(string connectionString)
        {
            string GetMyAssignmentsSQLFile = File.ReadAllText(@".\SQL\MyOpenAssignments.sql");

            var myAssignments = new ObservableCollection<MyAssignments>();
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {

                    conn.Open();
                    if (conn.State == System.Data.ConnectionState.Open)
                    {
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = GetMyAssignmentsSQLFile;
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    var myAssignment = new MyAssignments();

                                    myAssignment.CallID = reader.GetString(0);
                                    myAssignment.RecvdDate = reader.GetString(1);
                                    myAssignment.CallStatus = reader.GetString(2);
                                    myAssignment.Priority = reader.GetString(3);
                                    myAssignment.Classification = reader.GetString(4);
                                    myAssignment.CallDesc = reader.GetString(5);
                                    myAssignment.CloseDesc = reader.GetString(6);
                                    myAssignments.Add(myAssignment);
                                }
                            }
                        }
                    }
                }
                return myAssignments;
            }
            catch (Exception eSql)
            {
                Debug.WriteLine("SQL-Exception: " + eSql.Message);
            }
            return null;
        }
    }
Frantumn
  • 1,725
  • 7
  • 36
  • 61
  • Take a look and the accepted answer in this post and the one below it https://stackoverflow.com/questions/34430550/a-connection-was-successfully-established-with-the-server-but-then-an-error-occ – Y.S Sep 06 '18 at 18:26
  • Maximum connections is already set at 0. Authentication is already set for SQL Server and Windows Auth. Thanks though! – Frantumn Sep 06 '18 at 18:44
  • Have you added 1433 port to Inbound rule? please refer this case [reply](https://stackoverflow.com/questions/52154445/cannot-connect-uwp-app-to-sql-server-2017-instance/52161730#52161730). – Nico Zhu Sep 07 '18 at 02:24
  • That’s a different issue unfortunately. Not the cause here. – Frantumn Sep 07 '18 at 19:33
  • 1
    SQL Server 2008 R2 has been out of mainstream support for a long while, and it's got less than a year of extended support going. It wouldn't completely surprise me if this is an incompatibility/bug in the ADO.NET code, although it could also be a problem with the AOT compiler in UWP. Does it work when using SQL auth? If so, you've narrowed down the problem to something in the SSPI layer. Does the SQL Server error log say anything? Does it work when *not* using UWP, but (say) .NET Framework 4.7.2? – Jeroen Mostert Sep 10 '18 at 14:24
  • @jeroen you may be right. I have a read-only, SQL Auth account that works with older, non UWP apps. I was also able to create this app concept as a windows forms app in .NET using a data table but I really want to get it working on UWP. – Frantumn Sep 10 '18 at 14:31
  • That does suggest the ADO.NET stack as present in UWP is the problem, but troubleshooting such things in a reproducible matter is not trivial, and fixing them even less so -- not even with a bounty. :-) That said, you can still try UWP + sql auth -- beyond the SSPI handshake necessary for integrated auth and ignoring encrypted connections (which aren't used here) the rest of the TDS protocol is pretty much smooth sailing. (I know because I briefly dabbled in writing a TDS proxy.) You could also consult the security event log on the SQL Server, to see if there's anything interesting there. – Jeroen Mostert Sep 10 '18 at 14:44
  • Have you try this https://serverfault.com/questions/346525/cannot-login-to-sql-server-2008-r2-with-windows-authentication. Besides, have you adding a specific port for your connection? I once faced similar problem, the server maintainer just simply open a new port for me and solved the problem. – MT-FreeHK Sep 11 '18 at 01:31
  • Since you have already tried multiple solutions without any luck, this seems more like an R&D task. See if you can upload a trimmed down version of your code where you can reproduce the same problem for someone here to download and troubleshoot to help you. You should also provide your environment details along with the tools you are using. Waiting for someone with exact same problem as yours and provide a hint/solution might not help much. – dj079 Sep 11 '18 at 02:11
  • @Frantumn Not sure if you have gone over this article https://learn.microsoft.com/en-us/windows/uwp/debug-test-perf/ – Vikas Sharma Sep 17 '18 at 10:54
  • The earliest phases of the login process aren’t really exposed to .Net - they go through SNI. I would start looking at this with Wireshark/Message Analyzer. Is there a TCP handshake? Who is sending an RST/FIN? Does the client start PRELOGIN? Does TLS negotiation start? – Mitch Sep 17 '18 at 12:47

4 Answers4

1

Try using SQL Server Authentication instead of Windows Authentication, what means setting Integrated Security=False; and including user & password among other parameters in your connection string.

ConnectionString="Server=tcp:devsqlserverXXXX.database.windows.net,1433;Initial Catalog=XXXX;Persist Security Info=False;User ID=USER_ID_XXXX;Password=pa$$wordXXXX; MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=90;";

ArBR
  • 4,032
  • 2
  • 23
  • 29
  • I did this, with your exact string template, and a few other times with a few tweaks to trial. Nothing worked. The SQL Auth provided me with the same error. Connected, but error on the logon process. – Frantumn Sep 10 '18 at 18:13
  • @Frantumn: Use a SQL server Azure Instance, it's a matter of a pair of ours for setting it up. Use an appropiate connection string (see https://www.connectionstrings.com/sql-azure/), so you can have an alternative SQL Server environment. – ArBR Sep 10 '18 at 18:38
  • I'm in a very big enterprise with a few hundred servers. Some are new, some aren't. I know moving to newer hardware / systems could resolve the problem, but that's not an option here unfortunately. The database I'm connecting to is a vendors system and 1. we are limited to what they support 2. we aren't in a position to upgrade any time soon. – Frantumn Sep 10 '18 at 18:40
1

Could it be that your user has access to the database server but not the database?

LosManos
  • 7,195
  • 6
  • 56
  • 107
  • Not the case here. Unable to connect to it with the same credentials directly through sql management studio 17. – Frantumn Sep 15 '18 at 22:50
  • Now you have a better case when calling the support/responsible as you can say "SSMS" instead of "my program that should work". Call them and say you have a usr/pwd that should work but does not and ask for help. – LosManos Sep 16 '18 at 05:52
1

This issue seems to be reproducible with SQL Server 2008 R2 RTM (10.50.1600) and Windows 10 1709. I cannot reproduce the issue with SQL Server 2008 R2 SP2. I believe this is a Microsoft bug in either the server and the client.

See also:

The most expedient solution would certainly be to update your SQL Server to SP3 (or SP2).

Alternatively, consider using a more up-to-date SQL Server to proxy the connection via Linked Servers. Alternatively, use UWP Brokered Components or a WCF/REST middle-tier to proxy things on the client side.

Mitch
  • 21,223
  • 6
  • 63
  • 86
1

Ensure that the database name is correct. I had the problem (same error message) and all I did to fix it is fix the database name.

I do not see a database name in the connection string but I might not know how it is properly specified. For the future though, check the database name.

Sam Hobbs
  • 2,594
  • 3
  • 21
  • 32