locked
SQL 2014: login failed for user <DomainName\UserName> after upgrading to SQL Server 2014 and converting to VS2013 RRS feed

  • Question

  • Hello Community

        I had a VS2008 app and converted the app to VS2013 that accessed a SQL Server 2008 database that is now a
    SQL Server 2014 database.

        All the users are still the same and all old applications connections to the database are the same and still work.

        The problems is I had to modify an app to call a new app I created in the same project as well as add a couple of new
    columns to the table that the app accesses, but now when I run the app that I modified that access the the other app
    that I wrote which access the same table it use to access except I added 2 new columns to the table an error comes up stating:

           " Unhandled exception has occurred in your application. If youj click continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Login failed for user <DomainName\UserName>"

         My question is since all I did was modify the app and create a new app for it to call the same database it had been calling,
    why am I getting this error since I am now running VS2013 and SQL Server 2014?

        Note: Am I supposed to recreate all users again and give all of them permissions all over again?

        Thank you
        Shabeaut

    Tuesday, March 15, 2016 9:31 PM

Answers

  • Hello Erland Sommarskog

        ClickOnce is when you go into the properties of a desktop application
    and click the "publish" button and then the install button.

        Your supposed to be able to set it up such that when a user accesses the application
    the lastest version of the application is downloaded onto the computer.

        Sometimes the publishing process won't succeed unless you provide
    a certificate the same way sometimes you need to provide a certificate
    when access apps on the internet.

        Shabeaut

    • Marked as answer by Shabeaut Friday, March 18, 2016 1:19 PM
    Friday, March 18, 2016 1:17 PM

All replies

  • If you installed a new instance, and copied the database by means of BACKUP/RESTORE, you need to transfer the logins from the old instance to new instance, yes. On the other hand, if you upgraded the existing instance, you should not have to do it.

    If you look in the SQL Server errorlog, you will find more details about the login error. Post the message here if you cannot figure out what it means.

    • Proposed as answer by Ice Fan Wednesday, March 16, 2016 8:02 AM
    Tuesday, March 15, 2016 10:03 PM
  • Hi Shabeaut,

    From your above description it did not sound like user permissions issue. your schema is changed now. it could be your code throwing the errors for unhandled exceptions. you need to troubleshoot your code.

    to make sure it is not a access issue do the following from your program.

    connect to the database with the same user and try to display couple of rows on a new page. this will prove that there is no access issue.

    Good Luck

    kumar

     

    Tuesday, March 15, 2016 10:11 PM
  • Please help to post the full error message in SQL Server Error log for analysis. Here is an article for your reference:

    https://msdn.microsoft.com/en-us/library/ms366351%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    Generally, it may be caused by the Authentication Mode you are using.

    If you are trying to connect using SQL Server Authentication, verify that SQL Server is configured in Mixed Authentication Mode and verify that SQL Server login exists and that you have spelled it properly. If you are trying to connect using Windows Authentication, verify that you are properly logged into the correct domain. It’s also probably because UAC. Try right clicking and running as administrator and seeing if it goes away.

    If it is not UAC, it could be that the user does not have rights. There is a Step-By-Step post on how to fix this: http://itproguru.com/expert/2014/09/how-to-fix-login-failed-for-user-microsoft-sql-server-error-18456-step-by-step-add-sql-administrator-to-sql-management-studio/

    Hope it could be helpful.

    Wednesday, March 16, 2016 6:39 AM
  • Hello Sommarskog

        The VS2008 and SQL2008 existed first.

        I installed VS2013 and SQL2014.

       Since VS2008 existed first, all I did was open the VS2008 database with 
    SQL2014 and have been using SQL2014 ever since .

       What I forgot to include in this question is that only when the users run
    the app against the database does the Login failed for user <DomainName\UserName>
    error appear.  But I have no problem running the same app that the users run!

        So the question is actually why do the users get the Login failed for user <DomainName\UserName> ?

        Shabeaut

       

    Wednesday, March 16, 2016 1:54 PM
  • Hello Kumar Muppa

        I logged on as the user and opened Sql Management Studio and
    queried the table but when the user runs the app to the part where
    the table gets queried that is when the error: Login failed for user <DomainName\UserName>
    appears.  The situation is that

        The VS2008 and SQL2008 existed first.

        I installed VS2013 and SQL2014.

       Since VS2008 existed first, all I did was open the VS2008 database with
    SQL2014 and have been using SQL2014 ever since .

    What I forgot to include in this question is that only when the users run
    the app against the database does the Login failed for user <DomainName\UserName>
    error appear.  But I have no problem running the same app that the users run!

        So the question is actually why do the users get the Login failed for user <DomainName\UserName> ?

        Shabeaut

    Wednesday, March 16, 2016 2:25 PM
  • Hello Community

        I had a system composed of VS2008 an SQL Server 2008.

        Some time later I installed VS2013 and SQL2014.

       Since VS2008 existed first, all I did was open the SQL2008 database with
    SQL2014 and have been using SQL2014 ever since and converted the VS2008 app to VS2014.

        One thing I forgot to mention which may or may not make a difference is that
    I backed up the SQL2014 database and copied it to the test computer and restored the SQL database
    to the test computer and  ran the apps and added the 2 columns to the tables on the
    test computer.

       I also I forgot to mention in this question is that only when the users run
    the app against the database does the Login failed for user <DomainName\UserName>
    error appear.  But I have no problem running the same app that the users run
    whether I run it from the test computer or production but the users get the
    Login failed for user <DomainName\UserName> no matter where they try to run it from (
    they used to be able to run the app that reads the table in production)!

        So the question is actually why do the users get the Login failed for user <DomainName\UserName>
    no matter where they run it from but I don't get the error at all ?

    Note: If I log onto the SQL Server as the user I can run a query from the database but when I run the
    app as the user I get that Login failed for user <DomainName\UserName>

        Shabeaut

    Wednesday, March 16, 2016 5:55 PM
  •     So the question is actually why do the users get the Login failed for user <DomainName\UserName> ?

    Well, why should they not? You installed a new instance of SQL Server. Did you add you their logins to the new instance? Or why you do expect it work?

    Your own login works because you presumably gave that as the admin user when you installed SQL Server.

    Did you look at the SQL Server log as I told you?

    Wednesday, March 16, 2016 10:08 PM
  •     I am running Windows 7.  Since I can run the app that queries the table I decided to go thru the same process on my computer with under the users id.

        It comes to find out when I build the application under the users name I get a certificate error, so create a certificate under each user I logon under, the build the application, next I log back on under my own username and "publish" the application.

        Finally, I go to the users desk and download the new version onto their machine, then I logon from their machines and the app connects , queries the table and returns the results as it did before.

        The thing I would like to ask you is :

            1- When using ClickOnce (which this app uses to Publish the app) how come when the user logs on to the app it doesn't just download the latest changes?

            2- Why did I have to log on as each user and create a certificate under their names instead of creating the certificate one time under my own username and publish?

            Thanks
            Shabeaut

    Thursday, March 17, 2016 7:01 PM
  • I don't know what ClickOnce is. You will need to find a forum where they talk about this product to ask questions about it.

    Having to create a certificate for each user does not sound exactly normal. Unfortunately, I am not able to piece together what you have done. Or even understand why you need a certificate in the first place.

    Friday, March 18, 2016 7:45 AM
  • Hello Erland Sommarskog

        ClickOnce is when you go into the properties of a desktop application
    and click the "publish" button and then the install button.

        Your supposed to be able to set it up such that when a user accesses the application
    the lastest version of the application is downloaded onto the computer.

        Sometimes the publishing process won't succeed unless you provide
    a certificate the same way sometimes you need to provide a certificate
    when access apps on the internet.

        Shabeaut

    • Marked as answer by Shabeaut Friday, March 18, 2016 1:19 PM
    Friday, March 18, 2016 1:17 PM
  • Thanks for the clarification. I don't seem to any such buttons in the desktop applications on my computer, though.

    In any case, I think you need to ask about ClickOnce in some more relevant forum, as it is not an SQL Server feature.

    Friday, March 18, 2016 10:07 PM