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

Viewed 7057

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

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

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

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.

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.

Related