0

I have been connecting to my local database (SQL Express) fine all day from my C# console application.

I have made an update, where I create a database and table from within the C# code using ADO.NET

When I do this, my normal connection string fails, which is

    Data Source=.\\sqlexpress;  Trusted_Connection=True;  Initial Catalog=ThisIsMyDataBase;

The error message is

Cannot open database "ThisIsMyDataBase" requested by the login. The login failed.\r\nLogin failed for user 'My-PC\\MrD'.

If I open the tables in SSMS, then I can access everything, using Windows Authentication.

I was under the impression that the Trusted_Connection=True would mean I have permission, and that I'm accessing it with my Windows account (not an SQL account) would mean I don't see this issue, but I'm clearly wrong. I'm not sure how to solve this.

MyDaftQuestions
  • 4,487
  • 17
  • 63
  • 120
  • please post your entire connection string.. – Sachu May 13 '15 at 10:47
  • That **is** the entire connection string @Sachu, and it's been working fine (when the database was created in SSMS), but with this update (where the database is created via my application) it no longer works... – MyDaftQuestions May 13 '15 at 10:48
  • Does your windows user actually have permission to look at the database? – SKull May 13 '15 at 10:50
  • @Mathew, I can only assume so, I have no problem accessing the data from within SSMS – MyDaftQuestions May 13 '15 at 10:50
  • please try to login using the user mentioned through sql server – Sachu May 13 '15 at 10:51
  • @Sachu, updated my post, it works fine if I log on via SSMS using Windows Autehntication – MyDaftQuestions May 13 '15 at 10:52
  • just give `User Instance=True' and try..don't think its needed but just give a try – Sachu May 13 '15 at 11:12
  • @Sachu, thanks for staying with me, but sadly, this didn't make a difference – MyDaftQuestions May 13 '15 at 11:22
  • try this 'Go to SQL Server >> Security >> Logins and right click on My-PC\MrD' and select Properties . Then select 'user mapping' on the left pane. Select the database Mydb then in database role at the botton tick dbowner and check the connection.. – Sachu May 13 '15 at 11:28
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/77706/discussion-between-sachu-and-mydaftquestions). – Sachu May 13 '15 at 11:33
  • @MyDaftQuestions use below string `@"Server=localhost; Database= MyDb;Integrated Security=SSPI;";` – Sachu May 13 '15 at 13:24

3 Answers3

0

You propably do not have the sql server login, which is associated with your WIN user, mapped to a database user.

Check that your model database (under System databases in management studio) has your WIN user listed under Security/Users. If not, add it. Then delete the MyDb database and create it again using you code. The CREATE DATABASE statement uses a copy of the model database to create the new database and its metadata.

prudentcoder
  • 371
  • 2
  • 8
  • @MyDaftQuestions, I updated my answer based on your create script – prudentcoder May 13 '15 at 12:49
  • I don't see it here, but, I don't see the My-PC\MrD in any of the other databases and I can access them fine – MyDaftQuestions May 13 '15 at 13:23
  • Ok, check the Server Roles of your WIN user under Security / Logins / right click on My-PC\MrD and select properties. If the sysadmin is selected, you have implicit access to all of the databases for that login. – prudentcoder May 13 '15 at 13:30
  • If you are using an OLE DB provider you should use `Integrated Security=SSPI;` instead of `Trusted_Connection=True;` in your connection string. – prudentcoder May 13 '15 at 13:40
  • And the data source should be like: `Data Source=.\SQLEXPRESS` with only one `\\`. – prudentcoder May 13 '15 at 13:58
  • Sorry, it's \\ because it's C# code.. I can't have a single, it'll act as an escape character – MyDaftQuestions May 13 '15 at 14:22
  • @MyDaftQuestions, you should use [string literals](http://www.dotnetperls.com/string-literal) for connection strings. And [this](http://stackoverflow.com/questions/5096125/max-database-name-length-in-sql-server) might explain your problem with the long database name. – prudentcoder May 15 '15 at 17:45
0

The error itself is saying the user dont have access failed for user 'My-PC\\MrD'. do a login for the user from sql server itself

Sachu
  • 7,555
  • 7
  • 55
  • 94
0

I have the solution but I don't understand it at all

The answer is to rename the database with less characters. My actual database was 27 characters long, I've just given it a 4 character long name (simply called My01) and it works fine.

MyDaftQuestions
  • 4,487
  • 17
  • 63
  • 120