0

I'm learning to use ASP.NET and I came across the following problem: I could not access my database using SqlConnection() because I got an error

Cannot open database "Sample" requested by the login. The login failed. Login failed for user 'Asus\Anne'

Code:

SqlConnection con = new SqlConnection("data source=(localdb)\\MSSQLLocalDB; Database=Sample;Integrated Security=True");

I looked into it and found out that maybe the problem was that the user didn't have permission to access the database.

I checked and I don't have indeed any user called 'Asus\Anne' under my database's users, but I do have a 'dbo' user (dbowner), which has the 'Asus\Anne' login.

So, I tried to change dbo's username to 'Asus\Anne' but it doesn't let me.

I also tried to add a new user to the database called 'Asus\Anne', with an 'Asus\Anne' login but I got an error saying the login is already taken (which makes sense).

I feel like I'm stuck. Any suggestion?

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Acla
  • 141
  • 3
  • 12
  • Ok, so after spending hours changing and creating users, nothing was working. I was able to change 'dbo' login and create an 'Asus\Anne' user with an 'Asus\Anne' login, and I was also able to create a user to authenticate with sql server. However, I still had the same problem, until I decided to create my database through SSMS instead of VS and that did the trick. I still don't know why but now I can authenticate with both Windows and SQL. Also, I read that restarting the server was important but my 'restart' option is always greyed out. – Acla Aug 15 '16 at 14:27

2 Answers2

1

If you want to Use Windows Authentication mode, your machine must be joined to the domain (or any trusted domain) used by SQL Server and you should use AD login which has granted permissions on the SQL Server.

Also check firewall settings for SQL Server ports, specify a port number if not default, check if SQL Server allows remote connections, check enabled protocols in SQL Configuration Manager.

It seems like your DB is on your local machine - try to use SQL Authentication and change your connection string to use SQL Authentication - remove "Integrated Security=True" and add sql login name and password.

Anton
  • 2,846
  • 1
  • 10
  • 15
1

You need to login in to the database with sysadmin role to change user roles of other users etc. In this scenario probably 'Asus\Anne' does not have such elevated role to modify other roles(i am assuming that you logged into sql using windows authentication).

Either ways you can create a new user role with SQL Authentication and provide it with the necessary user roles. You will have to remove "Integrated Security = true" and add login and password instead.

Ramki
  • 389
  • 1
  • 9