connection error with SQL connection string

Question

connection error with SQL connection string

I am following a white paper that is a simple VS application that uses .NET to connect to a SQL server 2012 database.

https://www.mssqltips.com/sqlservertip/5677/how-to-get-started-with-sql-server-and-net/

My host where my application resides is a windows 7 machine. I have a vmware virtual server that contains the SQL server 2012 enterprise edition installation on windows NT 6.2. The hostname for this virtual server is TESTBOX. The name of the SQL server instance is SQL12_TEST1. I can connect to the database from SSMS client from the windows machine but I get an exception when I run the app.

Here is my code within the VS project:

using System.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestSQLApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = @"Server =TESTBOX\SQL12_TEST1; Database  = master; Trusted_Connection = True;";
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    string query = @"SELECT @@VERSION";
                    SqlCommand cmd = new SqlCommand(query, conn);
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            Console.WriteLine(dr.GetString(0));
                        }

                    }
                    else
                    {
                        Console.WriteLine("No data found.");
                    }
                    dr.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
            }
        }
    }
}

The project compiles. When I run, I get an exception: Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll

Here is the SQL server log file:

2019-08-01 15:56:06.91 Server      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
    Feb 10 2012 19:39:15 
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

2019-08-01 15:56:06.92 Server      (c) Microsoft Corporation.
2019-08-01 15:56:06.92 Server      All rights reserved.
2019-08-01 15:56:06.92 Server      Server process ID is 1364.
2019-08-01 15:56:06.93 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
2019-08-01 15:56:06.93 Server      Authentication mode is MIXED.
2019-08-01 15:56:06.93 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12_TEST1\MSSQL\Log\ERRORLOG'.
2019-08-01 15:56:06.93 Server      The service account is 'NT Service\MSSQL$SQL12_TEST1'. This is an informational message; no user action is required.
2019-08-01 15:56:06.94 Server      Registry startup parameters: 
     -d C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12_TEST1\MSSQL\DATA\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12_TEST1\MSSQL\Log\ERRORLOG
     -l C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12_TEST1\MSSQL\DATA\mastlog.ldf
2019-08-01 15:56:06.94 Server      Command Line Startup Parameters:
     -s "SQL12_TEST1"
2019-08-01 15:56:07.43 Server      SQL Server detected 2 sockets with 1 cores per socket and 1 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2019-08-01 15:56:07.43 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2019-08-01 15:56:07.43 Server      Detected 2047 MB of RAM. This is an informational message; no user action is required.
2019-08-01 15:56:07.43 Server      Using conventional memory in the memory manager.
2019-08-01 15:56:09.61 Server      This instance of SQL Server last reported using a process ID of 1368 at 8/1/2019 3:55:37 PM (local) 8/1/2019 7:55:37 PM (UTC). This is an informational message only; no user action is required.
2019-08-01 15:56:09.62 Server      Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2019-08-01 15:56:09.63 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.
2019-08-01 15:56:09.65 Server      Software Usage Metrics is disabled.
2019-08-01 15:56:09.70 spid8s      Starting up database 'master'.
2019-08-01 15:56:10.04 Server      CLR version v4.0.30319 loaded.
2019-08-01 15:56:10.41 spid8s      Resource governor reconfiguration succeeded.
2019-08-01 15:56:10.42 spid8s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2019-08-01 15:56:10.43 spid8s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2019-08-01 15:56:10.99 spid8s      SQL Trace ID 1 was started by login "sa".
2019-08-01 15:56:10.99 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2019-08-01 15:56:11.09 spid8s      Server name is 'TESTBOX\SQL12_TEST1'. This is an informational message only. No user action is required.
2019-08-01 15:56:11.27 spid13s     A self-generated certificate was successfully loaded for encryption.
2019-08-01 15:56:11.49 spid13s     Server is listening on [ 'any' <ipv6> 1433].
2019-08-01 15:56:11.52 spid13s     Server is listening on [ 'any' <ipv4> 1433].
2019-08-01 15:56:11.54 spid13s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL12_TEST1 ].
2019-08-01 15:56:11.55 spid13s     Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL12_TEST1\sql\query ].
2019-08-01 15:56:11.58 Server      Server is listening on [ ::1 <ipv6> 53312].
2019-08-01 15:56:11.58 Server      Server is listening on [ 127.0.0.1 <ipv4> 53312].
2019-08-01 15:56:11.58 Server      Dedicated admin connection support was established for listening locally on port 53312.
2019-08-01 15:56:11.63 spid13s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2019-08-01 15:56:11.63 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2019-08-01 15:56:11.64 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/TestBox:SQL12_TEST1 ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2019-08-01 15:56:11.64 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/TestBox:1433 ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2019-08-01 15:56:25.56 spid14s     A new instance of the full-text filter daemon host process has been successfully started.
2019-08-01 15:56:25.71 spid9s      Starting up database 'mssqlsystemresource'.
2019-08-01 15:56:25.71 spid17s     Starting up database 'msdb'.
2019-08-01 15:56:25.71 spid18s     Starting up database 'AdventureWorks'.
2019-08-01 15:56:25.72 spid9s      The resource database build version is 11.00.2100. This is an informational message only. No user action is required.
2019-08-01 15:56:25.75 spid19s     Starting up database 'TSQL2012'.
2019-08-01 15:56:25.80 spid20s     Starting up database 'AdventureWorksDW'.
2019-08-01 15:56:25.90 spid9s      Starting up database 'model'.
2019-08-01 15:56:26.13 spid9s      Clearing tempdb database.
2019-08-01 15:56:26.64 spid9s      Starting up database 'tempdb'.
2019-08-01 15:56:26.98 spid21s     The Service Broker endpoint is in disabled or stopped state.
2019-08-01 15:56:27.03 spid21s     The Database Mirroring endpoint is in disabled or stopped state.
2019-08-01 15:56:27.27 spid21s     Service Broker manager has started.
2019-08-01 15:56:27.54 spid8s      Recovery is complete. This is an informational message only. No user action is required.
2019-08-01 15:56:30.80 spid35s     AppDomain 2 (master.sys[runtime].1) created.
2019-08-01 16:18:16.11 Logon       Error: 17806, Severity: 20, State: 14.
2019-08-01 16:18:16.11 Logon       SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: fe80::b45a:c99f:5a55:1fd5%12]
2019-08-01 16:18:16.11 Logon       Error: 18452, Severity: 14, State: 1.
2019-08-01 16:18:16.11 Logon       Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: fe80::b45a:c99f:5a55:1fd5%12]
2019-08-01 16:18:46.07 Logon       Error: 17806, Severity: 20, State: 14.
2019-08-01 16:18:46.07 Logon       SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: fe80::b45a:c99f:5a55:1fd5%12]
2019-08-01 16:18:46.07 Logon       Error: 18452, Severity: 14, State: 1.
2019-08-01 16:18:46.07 Logon       Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: fe80::b45a:c99f:5a55:1fd5%12]

Can anyone help me build the correct connection string for my code? Do I need an IP address and what else?

sql

sql-server

asked on Stack Overflow Aug 1, 2019 by

Jason • edited Aug 1, 2019 by

Dale K

3 Answers

I think you may need to register your sql server, on the domain where your AD account lives, for SSPI to work.

The error seems to be telling you that "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: fe80::b45a:c99f:5a55:1fd5%12]"

Option B is use sql authentication with username and password in the connection string.

answered on Stack Overflow Aug 1, 2019 by

user2908532

An option might be to enable mixed mode for authentication and use a normal user for the connect.

As your lined document states:

Below, you can find examples of connection strings. The first one, uses a trusted connection, and the second one uses an SQL connection.

Trusted connection:

string connString = @"Server=INSTANCE_NAME;Database=DATABASE_NAME;Trusted_Connection = True;";

SQL Authentication-based connection:

string connString = @"Server=INSTANCE_NAME;Database=DATABASE_NAME;User ID=USERNAME;Password=PASSWORD";

In this demo, we are going to use a Trusted connection.

Mixed_mode=true defines to use Windows credentials and there is a Microsoft resource which explains potential root causes. but it also states that you should contact your administrator if you get state 1 back.

I think it could be related to authentication mode, service user configured for the sql server service or any other network related topic. Debugging the sql server might give you more insights about the problem.

But as the error tells something about windows authentication, I'd first try with user and password in the connection string. I hope this helps.

answered on Stack Overflow Aug 1, 2019 by

Christian

This would be easier to see if you looked at the exception message seen in your code, rather than trying to parse through the whole Sql Server log. Nevertheless, the the important part of the Sql Server Log is this:

The login is from an untrusted domain and cannot be used with Windows authentication.

You're probably running SSMS in the same VM where Sql Server is installed. It works there because it's the same operating system installation, and so integrated security works just fine. But you're not using an Active Directory domain, and when you run your app you're connected from a different system. Those two systems aren't setup to trust each other, and so the integrated security authentication isn't gonna work.

To get around this, you can run everything on the same operating system installation (all in the same VM -- ugh), put everything into an Active Directory environment (great if you've already got one, but ugh again if you don't), or use Sql authentication instead.

answered on Stack Overflow Aug 1, 2019 by

Joel Coehoorn

User contributions licensed under CC BY-SA 3.0