How can I troubleshoot: A connection was successfully established with the server, but then an error occurred during the login process

Question

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

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;
        }
    }

Answer 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;";

Answer 2

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