none
login failed for user c# 18456 RRS feed

  • Question

  • i can run the C# application i have created with the connection string

    Server=VISIONSQL\\VISION;Database=Vis;Trusted_Connection=True. 

    in windows profile ITsqlsyncservice. How can i create a connection string so that i will run on all profiles?

    I am trying to connect SQL server Database to a C# windows forms application. I have been looking through some connection string and found the below working when as it has access to the SQL server database.

    Server=VISIONSQL\\VISION;Database=Vis;Trusted_Connection=True

    I used to login to the windows as well as the SQL profile using a profile named ITsqlsyncservice to make the above connection string work.I can see and query the View that i am working on with ITsqlsyncservice. The password i have used is the same as the one i used to login to the SQl server

    But when i logged in as myself and run the windows forms application it didn't work (because i didn't have access to the SQL server instance ).

    So i have changed the query to the below to be able to run the application under my windows profile by providing ITsqlsyncservice username and password along with the connection string.

    sqlCon = new SqlConnection("Server=VISIONSQL\\VISION;Database=Vis;User Id=Domain\\ITsqlsyncservice;Password=<myPassword>;");

    but the above connection string is not working either.

    enter image description here


    • Edited by roma_victa Tuesday, May 22, 2018 9:45 AM
    Monday, May 21, 2018 8:52 AM

Answers

  • I did a quick test, you cannot use Integrated Security=SSPI with a user name as it is simply ignored. If you specify user ID then it will use SQL auth.

    class Program
    {
        static void Main ( string[] args )
        {
            var connStrings = new[]
            {
                @"Server=myserver;Database=mydatabase;User ID=windowsuser;Password=password",
                @"Server=myserver;Database=mydatabase;User ID=windowsuser;Password=password;Integrated Security=SSPI",
                @"Server=myserver;Database=mydatabase;User ID=domain\windowsuser;Password=password",
            };
    
            foreach (var connString in connStrings)
                TryConnect(connString);
    
            Console.ReadLine();
        }
    
        static void TryConnect ( string connectionString )
        {
            try
            {
                using (var conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                };
                Console.WriteLine($"({connectionString}) = Success");
            } catch (Exception e)
            {
                Console.WriteLine($"({connectionString}) - {e.Message}");
            };
        }
    }

    I'm afraid the only solution to using an arbitrary Windows account would be to impersonate the user before creating the connection. Then it'll work. But this will be slow if you do it a lot. If the program is a scheduled task or service then you can simply configure it to run as the appropriate Windows user and no impersonation is necessary.

    Note that in order to impersonate your code will need the user name and password. At that point you've lost the benefits of using Windows auth altogether. From a security point of view it would be better to switch to a SQL account that can be locked down by the DBAs and audited. This would help with accountability as well. It is generally recommended that you create accounts with only the permissions needed to do their jobs. In your case it would be the permissions needed to run your app's queries. 


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, May 23, 2018 1:57 PM
    Moderator

All replies

  • Hi roma_victa,

    Please look into the below article as it explains step by step. Hope this will help you.

    How to Fix Login Failed for User (Microsoft SQL Server, Error: 18456) Step-By-Step

    SQL Connection Strings


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Monday, May 21, 2018 2:50 PM
    Moderator
  • This doesn't sound like a C# issue. It sounds like a login issue. Have your DBA monitor the connection to see if it is getting there. Also verify the UN/PWD by connecting via SSMS. 

    Michael Taylor http://www.michaeltaylorp3.net

    Monday, May 21, 2018 2:50 PM
    Moderator
  • The first link is not working .

    but when i logged in to the windows profile with the same creadentials i used to logged in to the sql server and with the below connection string query it works.

    I didnt want it to be a windows authentication. i want to be able to key in the userid and password and create a setup so that i can install it to user's PC

     sqlCon = new SqlConnection("Server=VISIONSQL\\VISION;Database=Vision;Integrated Security=True;");

    Tuesday, May 22, 2018 7:24 AM
  • You're mixing Windows auth with your SQL auth. SQL user names don't have domain information. Remove the domain from the user name and ensure you're not specifying Integrated Security anywhere. Note that you can also use SQL Server Object Explorer in Visual Studio to connect to the SQL database instance using SQL. Then view the properties for the database and you will see the connection string you need to use.

    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, May 22, 2018 1:32 PM
    Moderator
  • Hi Taylor,

    Thanks for your replay. the only way i can get it to work is by login using ITsqlsyncservice to a windows profile and use Integrated Security to true in the connection string. when i login in to my profile the same connection string wont work. so i hardcoded the userid and password to the connection string as below.

    qlCon = new SqlConnection("Server=VISIONSQL\\VISION;Database=Vis;User Id=Domain\\ITsqlsyncservice;Password=<myPassword>;");

    now i understand that this is SQL Auth. what should i do to create a generic connection string so it will work on all the profile?

    Wednesday, May 23, 2018 4:33 AM
  • I'm afraid I don't understand what ITsqlsyncservice is. If you cannot log into SQL using SSMS then your .NET app isn't going to work either. Is this a networking thing? If you run your app as the Windows profile it works? If so then it sounds like a DB permission issue.

    I don't understand what you mean by a generic connection string. To connect as the currently logged in user remove the user name/password and set Integrated Security to SSPI. If you need to use a specific SQL account for all users then the user name/password combo is what you'd use. If you need to use a dedicated Windows account then the recommended approach would be to run your app in that context. Theoretically setting Integrated Security=SSPI in combination with user name/password would work but I don't know that I've ever seen it work.

    In general Windows auth is recommended. That means your DBA would need to configure the database to allow the user account(s) your app will run under with the appropriate permissions. If this is a general user app then this is the best approach because auditing will identify changes made to the DB by the users. If Windows auth isn't an option then the only other option you have is SQL auth. In that case it doesn't matter what the user is running the app as your connection string would define the user being used. If you need to dynamically change this as the app runs then you'd need to build the connection string manually. There is a connection string builder class for this if needed but it is often just easier to build it manually.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, May 23, 2018 4:58 AM
    Moderator
  • Thanks Taylor for such a detailed replay, ITsqlsyncservice is a domain account

    Currently managed to run the application using Integrated Security to true and did a run us using "ITsqlsyncservice "  in other profiles who need to run this application.

    Theoretically setting Integrated Security=SSPI in combination with user name/password would work but I don't know that I've ever seen it work.

    This will be ideal so that i do not have to run the application in the context of ITsqlsyncservice every time a user need to run this application.

    This is a very confidential database and ITsqlsyncservice  only have access to a view created by the DBA , I am not sure if i will get SQL account for this .

    Wednesday, May 23, 2018 9:47 AM
  • I did a quick test, you cannot use Integrated Security=SSPI with a user name as it is simply ignored. If you specify user ID then it will use SQL auth.

    class Program
    {
        static void Main ( string[] args )
        {
            var connStrings = new[]
            {
                @"Server=myserver;Database=mydatabase;User ID=windowsuser;Password=password",
                @"Server=myserver;Database=mydatabase;User ID=windowsuser;Password=password;Integrated Security=SSPI",
                @"Server=myserver;Database=mydatabase;User ID=domain\windowsuser;Password=password",
            };
    
            foreach (var connString in connStrings)
                TryConnect(connString);
    
            Console.ReadLine();
        }
    
        static void TryConnect ( string connectionString )
        {
            try
            {
                using (var conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                };
                Console.WriteLine($"({connectionString}) = Success");
            } catch (Exception e)
            {
                Console.WriteLine($"({connectionString}) - {e.Message}");
            };
        }
    }

    I'm afraid the only solution to using an arbitrary Windows account would be to impersonate the user before creating the connection. Then it'll work. But this will be slow if you do it a lot. If the program is a scheduled task or service then you can simply configure it to run as the appropriate Windows user and no impersonation is necessary.

    Note that in order to impersonate your code will need the user name and password. At that point you've lost the benefits of using Windows auth altogether. From a security point of view it would be better to switch to a SQL account that can be locked down by the DBAs and audited. This would help with accountability as well. It is generally recommended that you create accounts with only the permissions needed to do their jobs. In your case it would be the permissions needed to run your app's queries. 


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, May 23, 2018 1:57 PM
    Moderator