locked
Login Failed For User RRS feed

  • Question

  • So far I've written one .Net app that connects to SS, now I'm writing a second one but having a problem.

    I'm using SS Express and split into three databases due to the 10 gig limit.

    So far I've only tried it on one user machine. On this machine, I can connect to two of the databases, but 'login failed' for third.  I opened the login > UserMapping and see two of the databases checkmarked. The third (the one that's failing for the user) is not checked.

    So I checkmark it and click OK to complete the process and close the Login window. I see an hourglass for a couple of seconds and then it closes. No error message. But when I re-open the Login window to verify that the checkmark 'took', it's gone. I'm guessing the lack of a checkmark is the root of the failed login but  I don't get why it won't tick.


    • Edited by jal2 Tuesday, April 22, 2014 5:05 PM
    Tuesday, April 22, 2014 5:05 PM

Answers

  • I seem to have a solution (at least for the moment it's working so let's keep our fingers crossed).

    In SSMS I deleted both of the logins that I was attempting to work with - and deleted the associated Users.

    [NT Authority\Authenticated Users]

    [BUILTIN\Users]

    and then recreated them all. This time I granted the Users the roles db_DattaReader and db_Datawriter (because I was feeling desperate, will test on this later to see if unnecessary).  And I re-ran my Grant EXECUTE script for all stored procedures.

    And now I can connect to all three databases.   Thanks so much for your time!

    • Marked as answer by jal2 Wednesday, April 23, 2014 4:52 PM
    Wednesday, April 23, 2014 4:50 PM

All replies

  • Additonal info
    - Windows authentication
    - This is a Windows Domain
    - The Login is NT Authority\Authenticated Users.
    - I've also tried BuiltIn\Users same problem
    - The three databases are in a yellow folder called Index. On that folder I granted Modify permission to the two accounts above.
    - All three DBs show at least the following two users:
    (1) NT Authority\Authenticated Users.
    (2) BuiltIn\Users same problem
    I'm running SS under the Local Service account.
    - I granted Execute permission to all three DBs on all stored procedures.

    Tuesday, April 22, 2014 5:22 PM
  • The user that is being used here, did you check what is the server level security? The user mapping does not matter if the user is a sysadmin.

    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    Tuesday, April 22, 2014 6:14 PM
  • Hi,

    The problem is not clear to me. Can you plaease answer several Q:

    * My first guess(more feeling then guess) is that one database is not attach to the server.

    1. Do you use the database as embedded or did you attach them to the server first? If you attached them, can u see them in SSMS? If you work as embedded  database then I recomend to atach them (at least for the monitoring)

    2. can you post screen shots of what you are saying?

    3. is the problem only with the log-in and from the application you work OK with the datbases? if so can you post the connction string that you are using? Is the problem only when you use VS or from the SSMS as well? "opened the login > UserMapping" what log-in, the SSMS log-in windows, the Visul Studio Add connection window, or your app?

    4. If all databases are in the same folder then you can make sure they have the same permissions but delete ll permissions (and in the advance clear the Enable inheritance. Then bring back the Enable inheritance and add the permissions that you need (This action I can not recomend and if you do it you have to be the Administrator... I would probably do it)

    5. check the connection string at the x:\Windows\Microsoft.NET\Framework64\[version]\config\machine.config file. since 2 files work OK and one not his is low chance but...

    * I think that the basic monitoring should focus on the differenties of the database that work OK and the one that is not, once the basic thinks are chcked.


    [Personal Site] [Blog] [Facebook]signature

    Tuesday, April 22, 2014 6:45 PM
  • Thanks everyone! I'll try to address your questions more in depth shortly but for now -  The user has zero roles (no database roles, no server roles) - all he  has is Execute permissions.

    From the development machine (my machine) the .Net app works fine, I can connect to all three databases.

    I use a function to generate the connection string, I'm confident the parameters are correct (although I'll retest when I get a chance, she's back at her desk so I can't test on her machine right now)  because it's working fine for 2 of the databases:

            private string strCN(string DB, string ServerName) {
                string str = @"Data Source=ComputerName, PortNumber\.InstanceName; Integrated Security = SSPI; Database=" + DB;
                str = str.Replace("ComputerName", ServerName);
                str = str.Replace("PortNumber", "44444");
                str = str.Replace("InstanceName", "SqlExpress");
                return str;
            }

    Tuesday, April 22, 2014 7:59 PM
  • When I said I opened the Login window, I mean in SSMS  > Object Explorer > Security node > Logins > doubleclicked the Login  > User Mapping at the Left > Checkmark the "Map" column for each of the three databases, but one of the three checkmarks won't 'take' (well it seems to take until I reopen the window later on and see the checkmark is gone).
    Tuesday, April 22, 2014 8:02 PM

  • * I think that the basic monitoring should focus on the differenties of the database that work OK and the one that is not, once the basic thinks are chcked.



    I agree, but so far the only difference I can find is that the checkmark 'takes' for two of them and not for the third. Which is weird because the three DBs should be the same. After all, I started with only one database (let's call it the 'original' database. When it started to get large, I used the 'generate scripts' option to clone out two more DBs.  The two clones are working fine, the 'original' is giving me the problem.
    Tuesday, April 22, 2014 8:12 PM
  • 1. Do you use the database as embedded or did you attach them to the server first? If you attached them, can u see them in SSMS? If you work as embedded  database then I recomend to atach them (at least for the monitoring)


    I'm guessing 'embedded database' means installed locally. No the users/clients don't have SqlExpress installed on their machine. All the DBs exist on my development machine as the server and were created in SSMS. The client application (written in Csharp) doesn't create any databases, it merely connects remotely using a port number.
    Tuesday, April 22, 2014 8:18 PM

  • 4. If all databases are in the same folder then you can make sure they have the same permissions but delete ll permissions (and in the advance clear the Enable inheritance. Then bring back the Enable inheritance and add the permissions that you need (This action I can not recomend and if you do it you have to be the Administrator... I would probably do it)


    All the DBs seem to have the same permissions. They are all in the same folder and I gave permission to the folder (not to the individual DBs). When I right-click the DBs, it shows three accounts "Owner Rights", "Administrators", "Mssql$express" but when I right-click the folder it shows "Authenticated Users" and "users" and "Administrators" and "System" - all of which have at least Modify permission. This is a folder I created on my Windows 7 C-drive exclusively for this .Net project, it has no files or folders other than the 3 DB files (well six if you include the log files).
    Tuesday, April 22, 2014 8:24 PM

  • Here's the error message directly from the Sql Server ErrorLog file although I masked the domain\username to post it on this thread:

    "Login failed for user 'DomainName\Username'. Reason: Failed to open the explicitly specified database 'Year2014_835_1'.

    Now here's a copy and paste of the db name from the SSMS GUI:

    Year2014_835_1

    As you can see I have spelled the name correctly in the .Net app (actually I pull it from a query so I'm pretty sure it's always spelled correctly).

    Tuesday, April 22, 2014 8:52 PM
  • When I said I opened the Login window, I mean in SSMS  > Object Explorer > Security node > Logins > doubleclicked the Login  > User Mapping at the Left > Checkmark the "Map" column for each of the three databases, but one of the three checkmarks won't 'take' (well it seems to take until I reopen the window later on and see the checkmark is gone).

    OK, Now I understnd the problem (I think) :-)
    The talking on .Net app confused me with several options regarding the Visual Studio. This is a pure SQL Server question (Using th SSMS with SQL Express) probably.

    I will pass on the answers to the evening (currently 12:51 Israel), hopefully I will have some beneficial Thueson. For the moment here are some importents points which are not directly related to the question but can help ou a lot:

    1. The fuction  that you are using is strongly recomended NOT TO USE!

    1.1 Do not ever build a connection string or a query by connecting several parameters into one string! This can be a security risk and a bad coding. I think it is more neat to use SqlConnectionStringBuilder Class: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx
    For example:

    System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
    builder["Data Source"] = "(local)";
    builder["integrated Security"] = true;
    builder["Initial Catalog"] = "AdventureWorks;NewValue=Bad";
    Console.WriteLine(builder.ConnectionString);

    This is the same as using the connection string:

    Source=(local);Initial Catalog="AdventureWorks;NewValue=Bad"; Integrated Security=True

    1.2 Avoid storing the connection string in your code if this fit your application architecture, retrieve it from a configuration file.  

    1.3 In any case I Strongly recommend to read about the different between a String and a Stringbuilder classes. Consider avoiding the buffer overhead of concatenation. The performance difference can be significant! All the logic of replacing and changing a string or combining several strings into one using several steps, is not a good idea. Each time you do it the object is rebuid and actually you are using a new string. It is best to do all in one statment, or thinking on using stingbuilder class. In our case since you are trying to build a connection string then it is best to use SqlConnectionStringBuilder as I mentioned above.

    * Check this example which i try to rank the best solution

    String a,b,c,d
    
    // Best solution but hard to read when become too complex
    String MyResult
    MyResult = a + b + c + d
    // this build 1 string in the proccess
    
    // Great solution and very easy to read
    StringBuilder MyResult = new StringBuilder();
    MyResult.Append(a);
    MyResult.Append(b);
    MyResult.Append(c);
    MyResult.Append(d);
    // etc.. etc.. etc..
    
    // Very bad solution as the number of steps grown, which is actually building 5 new strings in the process
    String MyResult = ""
    MyResult = MyResult + a
    MyResult = MyResult + b
    MyResult = MyResult + c
    MyResult = MyResult + d

    I hope this was useful :-)
    Hopefully I will have time to read/write and I will have a solution for you, then I will pass on the answers to the evening


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Wednesday, April 23, 2014 10:43 AM
    Wednesday, April 23, 2014 10:39 AM
  • Thanks, although StringBuilder isn't new to me.  I'm aware that .Net strings are immutable. (I've actually got a couple of years  .Net programming under my belt). In this situation I see no terribly pressing need for StringBuilder since I only build the connection string once for the entire user session.

    Security is not a huge issue in this application - and even if it were, most users in my company can barely even figure out how to open up a Word document much less hack into a database!

    I just need the user to be able to connect to that third DB.

    Wednesday, April 23, 2014 3:59 PM
  • I seem to have a solution (at least for the moment it's working so let's keep our fingers crossed).

    In SSMS I deleted both of the logins that I was attempting to work with - and deleted the associated Users.

    [NT Authority\Authenticated Users]

    [BUILTIN\Users]

    and then recreated them all. This time I granted the Users the roles db_DattaReader and db_Datawriter (because I was feeling desperate, will test on this later to see if unnecessary).  And I re-ran my Grant EXECUTE script for all stored procedures.

    And now I can connect to all three databases.   Thanks so much for your time!

    • Marked as answer by jal2 Wednesday, April 23, 2014 4:52 PM
    Wednesday, April 23, 2014 4:50 PM
  • Hi,

    Thanks for inform us of the solution (or workaround) :-)
    I have just connected to the forum, so you save me time to read all the thread :-)

    If there will be any future problem regarding this issue pls remmember to add a link to this thread.

    * I just mntion StringBuilder as this is more common known, but what you need here in myopinion (according the short informaion we have here), is to use SqlConnectionStringBuilder :-)


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, April 23, 2014 5:50 PM