RCXDude

I'm trying to use DataContext.CreateDatabase(), and I can't get past the following error:

Cannot open database "testdb" requested by the login. The login failed. Login failed for user 'user'.

The following code causes the error:

Code Snippet - ERROR

// Causes error on last line

SqlConnection connection = new SqlConnection( );


SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("Data Source=.\\SQLEXPRESS;Persist Security Info=True;User ID=user;Password=password;Connect Timeout=30;User Instance=False" );

builder.InitialCatalog = "testdb";

connection.ConnectionString = builder.ConnectionString;

KolabDBDataContext dataContext = new KolabDBDataContext( connection );

dataContext.CreateDatabase();


Interestingly enough, however, the following works perfectly:

Code Snippet - Working
// Works fine:
SqlConnection connection = new SqlConnection( );

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("Data Source=.\\SQLEXPRESS;Persist Security Info=True;User ID=user;Password=password;Connect Timeout=30;User Instance=False" );

connection.ConnectionString = builder.ConnectionString;

connection.Open();

SqlCommand command = new SqlCommand( "CREATE DATABASE testdb", connection );

command.ExecuteNonQuery();

connection.Close();


I know, therefore that my connection is correct, as I can create a database normally, but I cannot use CreateDatabase() to do it. Any idea why this is

-Edit-

It is probably worth noting that I am using Visual Studio 2008, beta 2, SQL Server 2005 Express Edition, the .Net framework 3.5 beta 2, and Windows Vista.


Re: LINQ Project General Linq to SQL Creating Databases

Kathy Lu - MSFT

Hi,

I've tried on my system (same as your specs above: VS2008beta2, SQLExpress2005, Windows Vista). However, I don't have any users established on my server and so I'm using trusted connection instead of User ID/Password combination and couldn't reproduce your issue.

Does the same thing occur when you use trusted connection

Try using this connectionString:

Data Source=.\\SQLEXPRESS;Persist Security Info=true;Connect Timeout=30;User Instance=false;Trusted_Connection=sspi");

Thanks,

Kathy





Re: LINQ Project General Linq to SQL Creating Databases

RCXDude

Yes it fails with that connection string as well, in the same way.

I also note that I have tried both a simple dbml file and a completely empty one with identical results.




Re: LINQ Project General Linq to SQL Creating Databases

Kathy Lu - MSFT

It looks like you're connecting to the server via testdb to create the database using your KoLabDBDataContext (from the error message: "Cannot open database "testdb" requested by the login. The login failed. Login failed for user 'user'.". Is the KoLaDBDataContext have a database name "testdb"

Did you use the OR designer to originally create your datacontext

Can you try using this ConnectionString to connect to the master database first :

Data Source=.\\SQLEXPRESS;Initial Catalog=master; Persist Security Info=true;Connect Timeout=30;User Instance=false;Trusted_Connection=sspi");





Re: LINQ Project General Linq to SQL Creating Databases

RCXDude

I started using the name testdb instead of kolabdb when I was working with testing the code. It doesn't work at all with either database.

Connecting to databases has worked flawlessly (as demonstrated by my initial post). I can connect to any database, so long as I created it already. Connecting to databases with DataContext objects works. It's just database creation that fails.

The string above works fine if I try to connect to it, but would not work if I try creating a database on it, as this string would try to create the master database.

I did use the designer to create the datacontext.




Re: LINQ Project General Linq to SQL Creating Databases

Dariusz Jankowski

Check if your sa account is not disabled.

If you change authenticaton from "Windows" to "SQL Server" sa account is still disabled.

To enable sa account do this on SQL Server:

Code Snippet
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'password' ;
GO


If it still does not work:

Try to create your DataContext without Builder and don't add "Initial Catalog" parameter to connection string (!)

Code Snippet

KolabDBDataContext dataContext = new KolabDBDataContext( "Data Source=.\\SQLEXPRESS;Persist Security Info=True;User ID=sa;Password=password;Connect Timeout=30;User Instance=False" );

dataContext.CreateDatabase();





Re: LINQ Project General Linq to SQL Creating Databases

ben2004uk

I take it the user your logging in with is actually in the dbcreator role with correct permissions for everything

For example, my account which I use to login to windows is only in the public role on the server so running this code will cause an error.

Code Snippet

NorthwindDataContext dataContext = new NorthwindDataContext("Data Source=.\\SQLExpress;Initial Catalog=TestDB;Connect Timeout=30;User Instance=false;Trusted_Connection=sspi;");

try

{

if (!dataContext.DatabaseExists())

{

Console.WriteLine("Creating DB");

dataContext.CreateDatabase();

Console.WriteLine("Created");

}

else

{

Console.WriteLine("Deleting DB");

dataContext.DeleteDatabase();

Console.WriteLine("Creating DB");

dataContext.CreateDatabase();

Console.WriteLine("Created");

}

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

However, if I run it with Administrator permissions, then I can create the database. So check your permissions on the actual server. You can login to SQL Express using SQL Server Management Studio if you have it installed.

Also, SA password should never ever be password Smile

Hope this helps.

Ben






Re: LINQ Project General Linq to SQL Creating Databases

Dariusz Jankowski

Ben, RCXDude wanted to connect using SQL Server authentication not Windows Authentication - please see your code.

Because RCXDude is here I think He has so much wisdom to know about rules of creating password strings and can read and modify code. Smile





Re: LINQ Project General Linq to SQL Creating Databases

RCXDude

The problem here is a LINQ issue, I think, not a db issue.

This code:

Code Snippet - ERROR

// Create a connection

SqlConnection connection = new SqlConnection( );


// Define a connection string. User and Password are assumed to the correct credentials of an sa account.

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("Data Source=.\\SQLEXPRESS;Persist Security Info=True;User ID=user;Password=password;Connect Timeout=30;User Instance=False" );


// Set the InitialCatalog to indicate which DB I want to create:

builder.InitialCatalog = "testdb";


// Output the string

connection.ConnectionString = builder.ConnectionString;


// Create a data context using this string

SomeDBContext dataContext = new SomeDBContext( connection );


// Attempt to create the database

// This line, and only this line, fails.

dataContext.CreateDatabase();


Does not work. This code:

Code Snippet - OK

// Create a connection

SqlConnection connection = new SqlConnection( );


// Define a connection string. User and Password are assumed to the correct credentials of an sa account.

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("Data Source=.\\SQLEXPRESS;Persist Security Info=True;User ID=user;Password=password;Connect Timeout=30;User Instance=False");



//Output the string

connection.ConnectionString = builder.ConnectionString;


// Open the connection

connection.Open();


// Create a command to create a database called testdb

SqlCommand command = new SqlCommand( "CREATE DATABASE testdb", connection );


// Execute the command

command.ExecuteNonQuery();


// Close the connection
connection.Close();


Works just fine. They are identical. The second was created by copy and pasting then modifying the first to use LINQ instead of an SqlCommand object. Both have been tested and perform as indicated.

I believe this code demonstrates that I can in fact connect successfully login to my server and create a database. the only outstanding question, I think, is why can't LINQ do it




Re: LINQ Project General Linq to SQL Creating Databases

Dariusz Jankowski

In bad code you used Initial Catalog - does not do it - some guy from Linq team got me this code and it's working ok. I had some problem like You and when I pass connection string to constructor of DataContext all work's fine.

So, change code to:

Code Snippet

string conn = "Data Source=.\\SQLEXPRESS;Persist Security Info=True;User ID=user;Password=password;Connect Timeout=30;User Instance=False");

SomeDBContext context = new SomeDBContext(conn);

context.CreateDatabase();

It must run.

If not - please reply.





Re: LINQ Project General Linq to SQL Creating Databases

ben2004uk

I might be going crazy here but consider this. First, create the database using either mine or Dariusz code. Then execute this code. It has the ability to drop the table but will not be able to create a database.

Code Snippet

string conn = "Data Source=.;Initial Catalog=TestDB;Persist Security Info=True;User ID=TestDB;Password=----;Connect Timeout=30;User Instance=False";

SqlConnection connection = new SqlConnection(conn);

NorthwindDataContext dataContext = new NorthwindDataContext(connection);

if (dataContext.DatabaseExists())

{

Console.WriteLine("Deleting DB");

dataContext.DeleteDatabase();

Console.WriteLine("Creating DB");

dataContext.CreateDatabase();

Console.WriteLine("Created");

}

Same happens with this code:

Code Snippet

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("Data Source=.;Persist Security Info=True;User ID=TestDB;Password=----;Connect Timeout=30;User Instance=False");

builder.InitialCatalog = "testdb";

SqlConnection connection = new SqlConnection();

connection.ConnectionString = builder.ConnectionString;

NorthwindDataContext dataContext = new NorthwindDataContext(connection);

if (dataContext.DatabaseExists())

{

Console.WriteLine("Deleting DB");

dataContext.DeleteDatabase();

Console.WriteLine("Creating DB");

dataContext.CreateDatabase();

Console.WriteLine("Created");

}

So the problem is not with logging into the database and executing commands. The problem is with logging in using a SqlConnection object as a parameter and issuing a CreateDatabase command.

Unless of course its just me. Definately needs the team looking at it.

Ben






Re: LINQ Project General Linq to SQL Creating Databases

RCXDude

Dariusz Jankowski wrote:

In bad code you used Initial Catalog - does not do it - some guy from Linq team got me this code and it's working ok. I had some problem like You and when I pass connection string to constructor of DataContext all work's fine.

So, change code to:

Code Snippet

string conn = "Data Source=.\\SQLEXPRESS;Persist Security Info=True;User ID=user;Password=password;Connect Timeout=30;User Instance=False");

SomeDBContext context = new SomeDBContext(conn);

context.CreateDatabase();

It must run.

If not - please reply.



Ok, now I'm getting somewhere. Removing intial cataloge works.

But then something else came up:

This code, interestingly enough, works now:

Code Snippet - Works

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TESTDBML
{
class Program
{
static void Main( string[] args )
{
string conn = "Data Source=.\\SQLEXPRESS;Persist Security Info=True;User ID=--;Password=-----;Connect Timeout=30;User Instance=False;Initial Catalog=test_db";
SomeDataContext context = new SomeDataContext(conn);

context.CreateDatabase();

Console.WriteLine( "OK!" );

Console.ReadLine();
}
}
}


This suggests that the database was at fault. But that suggestion seems discredited by the fact that standard SqlCommand objects worked just fine, and so did DBML, with the exception of database creation. Further, since I last tested, I have not changed the database server configuration at all.

In fact, the only things I see changed here from the previous code are:
  • I create the connection string directly, rather than from a ConnectionStringBuilder object
  • I put Initial Catalog at the end.
I suspect the first thus has something to do with the problem, though I have no idea what.

So while I don't really know why, DBML creation now works. Thanks for your help everyone!




Re: LINQ Project General Linq to SQL Creating Databases

Dariusz Jankowski

I found my e-mail from Mathew Charles (from Microsoft) after LINQ chat (December 2006).

I had a project where I created DataContext using SqlConnection.

When I tried to run CreateDatabase() method i had error.

I sent him source code.

After analyze Mathew answered:

Looks like you want to change your default constructor for AdventureWorks2000DataContext so that it calls the base DataContext constructor that takes a string - i.e. don¡¯t create the SqlConnection yourself. Also, your connection string shouldn¡¯t contain an initial catalog - use this form:

public AdventureWorks2000DataContext() : base(¡°Data Source=localhost;Integrated Security=True¡±)

Let me know if that works for you.

It worked.

In .NET 3.0 DataContext has added constructor with parameter string connectionString so you don't have to run base class.