How can I open database "MyDB" requested by the login. when the login failed?

0

I am installing SQL server express on the machine using AdvancedInstaller. AdvancedInstaller calls the following script to (successfully) create the DB:

-- Inline SQL script
IF NOT EXISTS(SELECT name FROM master.sys.databases WHERE name = N'MyDB')
BEGIN
    create database MyDB
END

As the installer continues, it also creates tables and stored procs (successfully) via the following SQL script:

USE[MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS(SELECT* FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Computers]') AND type in (N'U'))
BEGIN
CREATE TABLE[dbo].[Computers](
[ID]
[uniqueidentifier]
NOT NULL,

[Name] [nvarchar](50) NOT NULL,
CONSTRAINT[PK_Computers] PRIMARY KEY CLUSTERED
(
[ID] ASC

)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]
) ON[PRIMARY]
END

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS(SELECT* FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Computer_Add]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Computer_Add] AS' 
END
GO


ALTER PROCEDURE[dbo].[Computer_Add] 
-- Add the parameters for the stored procedure here
@Name nvarchar(50), 
@ID uniqueidentifier output
AS

    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- Insert statements for procedure here
        set @ID = NEWID();

        -- insert the user into the DB.
        insert into[Computers]
        Values(
                @ID,
                @Name
                )
    END

To uses this DB, I have a C# DLL that has a 'Db.ConnectionWrapper' access class. That class has a method called 'OpenConnection' which contains this:

private SqlConnection _connection;
public SqlConnection Connection
{
    get { return _connection; }
}

private void OpenConnection()
{
    _connection = new SqlConnection(connectionString);
    _connection.Open();
}

'connectionString' is a string property that contains the following:

Server=localhost\\SQL_EXPRESS;Integrated security = SSPI; database=MyDB;

Joe is in the Administrators group.User "Joe" runs the AdvancedInstaller program which first installs SQL server express and then AdvancedInstaller runs the script to create and install the DB. After the install is (successfully) completed, Joe then runs a windows desktop program that uses the DLL to access the DB. The code to access the DB works flawlessly. Joe then logs out from the server computer.

However, when "Bob" logs in to the server computer (again another user in the Administrator group) and runs the same windows desktop program, the following exception is thrown when the code tries to open the connection to the DB:

System.Data.SqlClient.SqlException(0x80131904): Cannot open database "MyDB" requested by the user.The login failed.
Login failed for user 'MPS\Bob'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Db.ConnectionWrapper.OpenConnection()

My question is why?

EDIT1:

Could it be because I do NOT have this statement during the creation of the DB:

ALTER DATABASE [PMInfrastructure] SET  MULTI_USER 

Update to EDIT1: Database is already in MULTI_USER mode. - so no is the answer to this.

EDIT2

SQL server is installed (successfully), by AdvancedInstaller, using a silent installation with ONLY these command line parameters:

/q /IACCEPTSQLSERVERLICENSETERMS /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=SQL_EXPRESS /UpdateEnabled=False

SQL actions taken

(per Raul Garcia's suggestion)

1> sqlcmd -E -S .\SQL_EXPRESS
2> go

1> USE [PMInfrastructure]
2> go

1> select name from sys.Login_token order by name
2> go
name
-------------------------------------------------
\Everyone
\LOCAL
BUILTIN\Administrators
BUILTIN\Users
BUILTIN\Users
CETRUS\SophosUser
MPS\Domain Users
MPS\SophosUser
NT AUTHORITY\Authenticated Users
NT AUTHORITY\INTERACTIVE
NT AUTHORITY\REMOTE INTERACTIVE LOGON
NT AUTHORITY\This Organization
public 
(13 rows affected)
1>

Here is what I got when I ran the commands

1> ALTER ROLE [db_datareader] ADD MEMBER [BUILTIN\Administrators]
2> ALTER ROLE [db_datawriter] ADD MEMBER [BUILTIN\Administrators]
3> GO
Msg 15151, Level 16, State 1, Server CETRUS\SQL_EXPRESS, Line 1
Cannot add the principal 'BUILTIN\Administrators', because it does not exist or
you do not have permission.
Msg 15151, Level 16, State 1, Server CETRUS\SQL_EXPRESS, Line 2
Cannot add the principal 'BUILTIN\Administrators', because it does not exist or
you do not have permission.
1>

SQL actions #2

C:\Program Files\Microsoft SQL Server\MSSQL12.SQL_EXPRESS\MSSQL\Binn


sqlcmd -E -S .\SQL_EXPRESS

use master
GO

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=PMInfrastructure
GO

USE [PMInfrastructure]
GO

ALTER ROLE [db_datareader] ADD MEMBER [BUILTIN\Administrators]
ALTER ROLE [db_datawriter] ADD MEMBER [BUILTIN\Administrators]
GO

quit

SQL Actions #2 Results:

C:\Program Files\Microsoft SQL Server\MSSQL12.SQL_EXPRESS\MSSQL\Binn>sqlcmd -E -S .\SQL_EXPRESS
1> use master
2> GO
Changed database context to 'master'.
1> CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=PMInfrastructure
2> GO
1> USE [PMInfrastructure]
2> GO
Changed database context to 'PMInfrastructure'.
1> ALTER ROLE [db_datareader] ADD MEMBER [BUILTIN\Administrators]
2> ALTER ROLE [db_datawriter] ADD MEMBER [BUILTIN\Administrators]
3> GO
Msg 15151, Level 16, State 1, Server CETRUS\SQL_EXPRESS, Line 1
Cannot add the principal 'BUILTIN\Administrators', because it does not exist or
you do not have permission.
Msg 15151, Level 16, State 1, Server CETRUS\SQL_EXPRESS, Line 2
Cannot add the principal 'BUILTIN\Administrators', because it does not exist or
you do not have permission.

SQL actions #3

I found this script:

USE master;
GO
CREATE LOGIN [BuiltIn\Users] FROM WINDOWS;
GO
USE PMInfrastructure;
GO
CREATE USER [BuiltIn\Users] FROM LOGIN [BuiltIn\Users];
GO
CREATE ROLE rSupport;
GO
EXEC sp_addrolemember 'rSupport', 'BuiltIn\Users';
GO
GRANT SELECT, INSERT,UPDATE, etc ON Mytable TO rSupport;
GO

I have two questions:

SQL_A_3_1: Since the sp_addRoleMember is deprecated I did this ?

ALTER ROLE rSupport ADD MEMBER FROM USER [BuiltIn\Users]

SQL_A_3_2: I then did this ?

GRANT ALL TO rSupport;

thanks

c#
sql-server
database
permissions
asked on Stack Overflow Sep 19, 2016 by MrLister • edited Sep 20, 2016 by MrLister

2 Answers

1

The most likely reason why the application works when “Joe” installed SQL & runs the app, and fails when “Bob” runs the app is that “Joe” is very likely a member of sysadmin group while “Bob” is not.

I noticed that the script to create the database doesn’t include the creation of any user, which implies that only sysadmin members would be able to connect to the database (not a recommended setting).

To know if I am correct, try the following using sqlcmd running as both Joe & Bob (i.e From the command line run sqlcmd -E -S .\sqlexpress).

SELECT name, type, usage FROM sys.login_token order by usage, type, name

If my theory is correct, you will see that Joe has an entry similar to this one, indicating that he is a member of sysadmin, while Bob doesn’t include it:

sysadmin    SERVER ROLE GRANT OR DENY

You can solve this by creating a user for Bob (or better yet for a Windows group that includes Bob & Joe), and grant him the necessary access to the database. For example:

USE [myDB];
go

CREATE USER [MPS\MyDB_group];
go

ALTER ROLE [db_datareader] ADD MEMBER [MPS\MyDB_group];
ALTER ROLE [db_datawriter] ADD MEMBER [MPS\MyDB_group];
Go

Please let us know if my original assumption is not correct, and we will recommend a different strategy to find out the root cause of the problem.

Thanks,

-Raul Garcia

SQL Security

answered on Stack Overflow Sep 19, 2016 by Raul G
0

Run Update-Database command after add-Migration. It will locate the database, and there won't be login failures anymore.

answered on Stack Overflow Jan 26, 2021 by Fahad Hussain • edited Jan 26, 2021 by double-beep

User contributions licensed under CC BY-SA 3.0