Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Problems with "Create User With Password"

Unanswered Problems with "Create User With Password"

  • Thursday, November 22, 2012 11:06 AM
     
     
    Sign In to Vote
    0

    Hi there,

    in my Situation "UserOne" is a contained database user (without sqllogin) and the user is dbo in the contained database.
    With this user i create other users with password in the contained database.
    The CREATE USER WITH PASSWORD Statement executed with the Entity Framework works and the new user is in the database.

    But after that, the next SQL-Access (e.g. SELECT * FROM Products) of "UserOne" through the Entity Framework crashes with the Error.
    2012-11-22 11:29:10.17 Logon       Login failed for user 'S-1-9-3-3794712727-1332191744-3365646766-2628027274.'. Reason: Could not find a login matching the name provided. [CLIENT: 127.0.0.1]
    2012-11-22 11:29:10.17 spid55      Error: 18056, Severity: 20, State: 5.
    2012-11-22 11:29:10.17 spid55      The client was unable to reuse a session with SPID 55, which had been reset for connection pooling. The failure ID is 5. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

    Whats the problem here? Do the user who whats to create other contained database users need a SQLServer Login?
    Because everything works fine, when "UserOne" has a SQLLogin!?

    But I thought that the sense of contained database users is that they don't need a SQLServer Login?

    Hope anyone can help me?

    Greetings
    Martin



    • Edited by FuE.Martin Thursday, November 22, 2012 11:08 AM
    •  
     

All Replies

  • Thursday, November 22, 2012 11:44 AM
     
      Has Code
    Sign In to Vote
    0

    Hello Martin,

    After creating the contained user, you need to assign him necessary permissions to access the database. In this case, you may need to grant Select permission on the selective table, or db_datareader permission, which assigns read permissions on all tables in the database.

    USE [demodb];
go
alter role db_datareader add member [userone];
go

    You may also need to consider that, you must explicitly specify the database name for a contained user, otherwise the user may not be able to login to database, as the default authentication of a Login is on master database.

    Hope, this may help :)

    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

     
  • Thursday, November 22, 2012 12:14 PM
     
      Has Code
    Sign In to Vote
    0

    Hello Sudeepta,

    thanks for your answer, but I think I do all of that and it doesnt work. Meanwhile I use a stored procedure for creating the contained database users.
    Maybe the problem relates to the Entity Framework Connection Pooling?
    There is no problem with any SQL statement my application fires until I come to the point of creating a new application user which is a new "user with password" in our contained database. The stored procedure executes without any error but the next SQL statement crashes.

    This is my stored procedure:

    USE [ixxais]
GO
/****** Object:  StoredProcedure [dbo].[AddUser]    Script Date: 22.11.2012 13:02:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddUser]
 @benutzer AS SYSNAME,
 @passwort AS SYSNAME
AS
BEGIN

 DECLARE @myError int

 EXEC('CREATE USER [' + @benutzer + '] WITH PASSWORD = ''' + @passwort + ''', DEFAULT_SCHEMA= [dbo], DEFAULT_LANGUAGE=[NONE];
       GRANT CONNECT TO [' + @benutzer + '];
    ALTER ROLE [db_owner] ADD MEMBER [' + @benutzer + '];
    GRANT ALTER ANY USER TO [' + @benutzer + ']')

 SELECT @myError = @@Error

 RETURN @myError

END

    Any other ideas?

     
  • Thursday, November 22, 2012 12:39 PM
     
     
    Sign In to Vote
    0

    Hello Martin,

    Did you try to execute the SP to create the contained user from Management Studio? If you are able to do that, then I think, it may be an issue with your front-end app (sorry, I may not be helpful in that).

    What error messages are logged in the SQL Server error log?

    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

     
  • Thursday, November 22, 2012 7:29 PM
     
     
    Sign In to Vote
    0

    Hi again,

    in the SQL Server error log I found:

    2012-11-22 11:29:10.17 Logon       Login failed for user 'S-1-9-3-3794712727-1332191744-3365646766-2628027274.'. Reason: Could not find a login matching the name provided. [CLIENT: 127.0.0.1]
    2012-11-22 11:29:10.17 spid55      Error: 18056, Severity: 20, State: 5.
    2012-11-22 11:29:10.17 spid55      The client was unable to reuse a session with SPID 55, which had been reset for connection pooling. The failure ID is 5. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

    Yes, my stored procedure works in SSMS - it works in my front end app too, but the next sql statement after the execution of the stored procedure failed, with the error you can read above (Login failed for user ...). Seems that the connection looses it's credentials or the fact that it is a contained database and therefore no need for a SQL Server login!?


    • Edited by FuE.Martin Thursday, November 22, 2012 7:35 PM
    •  
     
  • Friday, November 23, 2012 10:11 AM
     
     
    Sign In to Vote
    0

    Hello Martin,

    As I mentioned earlier, the contained user is trying to authenticate aganist the master database, that's why you got the above error.

    You have to provide the Contained database name along with the contained user name and password.

    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

     
  • Friday, November 23, 2012 12:24 PM
     
      Has Code
    Sign In to Vote
    0

    Hello Sudeepta,

    I can replay the error in a small c# codesnippet. The first interation works fine, the second interation crashes on executing the stored procedure with the known "Login failed" error. As you can see, in every interation a set the connection string and I create a new SqlConnection object with the connection string.

    Do you have another advice?

    Snippet:

    string connString;
            int lfdNr = 1;

            for (int i = 1; i <= 3; i++)
            { 
                connString ="data source=127.0.0.1;initial catalog=ixxais;User Id=Admin;Password=medatixx$2014;MultipleActiveResultSets=True;App=EntityFramework";
                using (var PubsConn = new SqlConnection(connString))
                {
                    SqlCommand testCMD = new SqlCommand("AddUser", PubsConn);
                    testCMD.CommandType = CommandType.StoredProcedure;
                    SqlParameter benutzer = testCMD.Parameters.Add("@benutzer", SqlDbType.VarChar, 128);
                    benutzer.Direction = ParameterDirection.Input;
                    SqlParameter passwort = testCMD.Parameters.Add("@passwort", SqlDbType.VarChar, 128);
                    passwort.Direction = ParameterDirection.Input;

                    benutzer.Value = "TestUser" + lfdNr.ToString();
                    lfdNr++;
                    passwort.Value = "abcdefgh$2014";
                    PubsConn.Open();
                    var teststring = PubsConn.ConnectionString;
                    var result = testCMD.ExecuteNonQuery();

                    testCMD = new SqlCommand("SELECT COUNT(*) FROM Benutzer", PubsConn);
                    testCMD.CommandType = CommandType.Text;

                    var count = testCMD.ExecuteScalar();

                    PubsConn.Close();
                }
            }

     
  • Friday, November 23, 2012 11:07 PM
     
     
    Sign In to Vote
    1

    I was able to reproduce the problem and I've reported it on
    https://connect.microsoft.com/SQLServer/feedback/details/772069/login-failure-with-connection-pooling-in-contained-db-after-connection-has-added-contained-user

    It only seems to happen when you add (contained) users to the database. With a plain command, things works as advertised.

    There is a simple workaround: add ;Pooling=false to the connection string. If my theory is correct, you only need this for this particular stored procedure.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Saturday, November 24, 2012 6:42 PM
     
     
    Sign In to Vote
    0

    Hi Erland,

    thank your for testing my problem and for posting it on connect.microsoft.com.