Ошибка cannot open database requested

connectionString="Server=.SQLExpress;Database=IFItest;User ID=xyz;pwd=top$secret"

7

SELECT SUSER_ID('xyzASPNET') -- (**not** SUSER_SID)

CREATE LOGIN [xyzASPNET] FROM WINDOWS

USE test
GO
SELECT USER_ID('xyzASPNET')

USE test
GO
CREATE USER [xyzASPNET] FROM LOGIN [xyzASPNET]

2

1

_dbContext.Database.EnsureCreated()

3

0

ex : xxx-db-dev to xxx-dev-db

1

1

USE ComparisonData// databaseName
EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='ftool',@LoginName='ftool';

0

string sqlString = " Data Source = (LocalDB)\MSSQLLocalDB;" + "AttachDbFilename = F:\.........\myDatabase.mdf; Integrated Security = True; Connect Timeout = 30";

1

I have not seen this mentioned in the previous issues, so let me throw out another possibility. It could be that IFItest is not reachable or simply does not exist. For example, if one has a number of configurations, each with its own database, it could be that the database name was not changed to the correct one for the current configuration.

If you haven’t created the database in your server you will get the same login error.Make sure that the database exist before you login.

it’s not a login issue most times. The database might not have been created. To create the database, Go to db context file and add this.Database.EnsureCreated();

0

Even if you’ve set the login as DB owner and set the user mapping for the database that will use the login, check that the actual DB user (not just the login) has the role of ‘owner’.

When using EF Code First, make sure the database exists. You could run the update-database command first.

1

If you didn’t have any problems before and you get this error only in the package manager console, you don’t need to do anything special. Just open the sql server object explorer window and connect and run the command again in the console.

• Remove From My Forums

Answers

• • Marked as answer by
    RueSam
    Friday, July 23, 2010 1:14 AM

All replies

• Is MyTrLog1 the database (catalog) that you’re trying to create?  If so, you cannot open it until after it’s created.  You need to use a connection string without a catalog — it’ll get set to the default for the user which is usually ‘master’.

• Is MyTrLog1 the database (catalog) that you’re trying to create?  If so, you cannot open it until after it’s created.  You need to use a connection string without a catalog — it’ll get set to the default for the user which is usually ‘master’.

  Thanks for the help.

  I tried the following:

  //Constructor
  txtConnString.Text = "Data Source=.\SQLEXPRESS;" +
          //"User Instance=True;" + 
          //"AttachDbFilename=|DataDirectory|\MyTrLog1.mdf;" + //Initial Catalog=MyTrLog1;
          "Integrated Security=True;";
  
        txtScript.Text = Path.Combine(Environment.GetFolderPath(
          Environment.SpecialFolder.ProgramFiles), 
          @"C & R Management Services, IncMyTruckLogCreateDatabase.sql");
  
  //Event handler
  try
        {
          if (File.Exists(txtScript.Text))
          {
            string script = File.ReadAllText(txtScript.Text);// file.OpenText().ReadToEnd();
            SqlConnection conn = new SqlConnection(txtConnString.Text);
            //conn.Open();
            Server server = new Server(new ServerConnection(conn));
            server.ConnectionContext.ExecuteNonQuery(script);
            //conn.Close();
          }
        }
        catch (SqlException sqx)
        {
          MessageBox.Show(sqx.Message);
        }
        catch (Exception ex)
        {
          MessageBox.Show(ex.Message);
        }
  
  

  And I got the following:

  An exception occurred while executing a Transct-SQL statement or batch.

  I am inclined to think that it is not the CreateDatabase.sql file itself that is causing this error message since I miraculously created the .mdf and .log files in a previous experiment yesterday — problem is that I still could not access the database
  when I started the program; that success occurred by compiling the create_database.exe and copying it to the target computer’s installed files directory.

  I subsequently deleted the databases created (on the target computer of course) to duplicate the success with the create_database.exe file being incorporated as part of the MyTruckLog.msi program.  No luck.  Since then I have no success with recompiling
  create_datase.exe and copying it to the target computer.

  Ruel

• Sounds like it could be a permissions issue.  What was the full detail on the exception including the stack trace and inner exceptions if any?

  Try running the installation program as an administrator.

• Sounds like it could be a permissions issue.  What was the full detail on the exception including the stack trace and inner exceptions if any?

  Try running the installation program as an administrator.

  Got the same message running as administrator.

• What was the full detail on the exception including the stack trace and inner exceptions if any?

• What was the full detail on the exception including the stack trace and inner exceptions if any?

  Only «An exception occurred while executing a Transct-SQL statement or batch.» appears in the message box on the target computer. 

  Which utility should I use on the target computer?

  Looking at the error log in \MSSQLLOG shows:

   «Dedicated administrator connection support was not started because it is not available on this edition of SQL Server.»

  «Login failed for user ‘ ‘.  The user is not associated with a trusted SQL Server connection.»

  Seems the latter sentence may be my problem?

• Hi,

  Please try changing the Authentication mode for that sql server to Mixed Authentication mode so that windows authentication will be enabled.

  [Please mark the post as answer if it answers your question]

  Thanks & Regards,

  Sai Pavan

• Hi,

  Please try changing the Authentication mode for that sql server to Mixed Authentication mode so that windows authentication will be enabled.

  [Please mark the post as answer if it answers your question]

  Thanks & Regards,

  Sai Pavan

  Sai,

  I think you may be pointing me in the right direction.

  If we are talking about sql server on the target computer, I would have to use code somewhere in the installation process to add the authentication mode to the registry — I presume before running the script to create the database? 

  And how does one handle the sa password on the target computer since a password is required with mixed mode authentication?

  Ruel

• Hi RueSam,

  I did some analysis and found out that there are only two logins available by default on SQLEXPRESS viz., sa and BuiltInUsers and sa is disabled and BuiltInUsers is not a sysadmin which means that you locked yourself out of the system. So you will not
  be able to execute any commands on the SQLEXPRESS. In order to resolve this issue run the SSMS using «Run as Administrator» and then connect to the SQLEXPRESS instance and enable the mixed mode authentication and sa accounts.

  In order to programatically do this

  First instantiate a cmd with adminstartor privileges using the following code can be used as guidance

  MyStartInfo.UseShellExecute = true;           
  
  MyStartInfo.Verb = «runas»;
  MyStartInfo.Arguments = «/env /user:» + «Administrator» + » cmd»;

  Now start the process (cmd with admin privileges) and run the following commands in it

  osql -S .SQLEXPRESS -E -Q «ALTER LOGIN sa ENABLE»

  osql -S .SQLEXPRESS -E -Q «ALTER LOGIN sa WITH PASSWORD = ‘abcd#123′»

  net stop MSSQL$SQLEXPRESS

  net start MSSQL$SQLEXPRESS

  Now you will be able to login using sa account. For this to work the authentication mode has to be in «Mixed Mode»

  [Please mark the post as answer if it answers your question]

  Thanks & Regards,

  Sai Pavan

• • Marked as answer by
    RueSam
    Friday, July 23, 2010 1:14 AM

• Received the following:

  Cannot open database «Databasename» requested by the login.  The login failed.  Login failed for user ‘ComputerUser’.

  Hi Ruel,

  The error is caused by the login does not have sufficient permissions on the database. Your connection string uses Windows Authentication, so the current Windows account will be used to connect to SQL Server.

  You can refer to the following links to grant permissions to the login, which is ‘ComputerUser’ in your case.

  http://forums.asp.net/t/1473123.aspx

  SQL SERVER – FIX : ERROR : Cannot open database requested by the login. The login failed. Login failed for user ‘NT AUTHORITYNETWORK SERVICE’.

  As the application needs to be deployed on multiple computers, this problem may occur at times. I suggest you use a feature called user instance to attach and connect to the SQL Server database.

  User instance is a feature of SQL Server Express Edition and is not supported by other editions of SQL Server (such as Developer or Enterprise). Attaching the database files with user instance is easy for deployment. It allows users who are not administrators
  on their local computers to attach and connect to SQL Server Express databases.

  If we want to work with SQL Server Express user instance, we need to put the database files in the “App_Data” folder and use a connection string like follows:

  Data Source=.SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|Test.mdf;user instance=true

  For more information, I would suggest you refer to the documents below:

  SQL Server 2005 Express Edition User Instances
  http://msdn.microsoft.com/en-us/library/bb264564.aspx

  Working with User Instances
  http://msdn2.microsoft.com/en-us/library/ms254504.aspx

  We can also embed SQL Server Express within our application so that SQL Server Express will be installed with our application. Please see:

  Embedding SQL Server Express into Custom Applications
  http://msdn.microsoft.com/en-us/library/bb264562(SQL.90).aspx

  Configuring SQL Express During Installation
  http://blogs.msdn.com/b/sqlexpress/archive/2006/09/20/configuring-sql-express-during-installation.aspx

  ---

  Please remember to mark the replies as answers if they help and unmark them if they provide no help.
  
  Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
  

  Thank you very much.  Your links led me to the MS Topic «Connecting to SQL Server Express User Instances (ADO.NET)» which in turn made me see the error I was making all along:  The name of the data file was MyTruckLog.mdf whereas the name of
  the database was MyTrLog1.  Even then I nearly went wrong before realizing that I had to delete the old log file in the target directory!

  Thanks once more.

  Ruel Samuels

• Hi RueSam,

  I did some analysis and found out that there are only two logins available by default on SQLEXPRESS viz., sa and BuiltInUsers and sa is disabled and BuiltInUsers is not a sysadmin which means that you locked yourself out of the system. So you will not
  be able to execute any commands on the SQLEXPRESS. In order to resolve this issue run the SSMS using «Run as Administrator» and then connect to the SQLEXPRESS instance and enable the mixed mode authentication and sa accounts.

  In order to programatically do this

  First instantiate a cmd with adminstartor privileges using the following code can be used as guidance

  MyStartInfo.UseShellExecute = true;           
  
  MyStartInfo.Verb = «runas»;
  MyStartInfo.Arguments = «/env /user:» + «Administrator» + » cmd»;

  Now start the process (cmd with admin privileges) and run the following commands in it

  osql -S .SQLEXPRESS -E -Q «ALTER LOGIN sa ENABLE»

  osql -S .SQLEXPRESS -E -Q «ALTER LOGIN sa WITH PASSWORD = ‘abcd#123′»

  net stop MSSQL$SQLEXPRESS

  net start MSSQL$SQLEXPRESS

  Now you will be able to login using sa account. For this to work the authentication mode has to be in «Mixed Mode»

  [Please mark the post as answer if it answers your question]

  Thanks & Regards,

  Sai Pavan

  Thank you for your input Sai.  Please see my reply to Jian Kang.  Ruel