CODEWITHSUNDEEP
sql-serverasp.net-mvcvisual-studiossms
Truecolor
Truecolor

Reputation: 469

How to make database go to SQL Server instead of localDB?

I am able to log into SSMS under Windows Authentication and SQL Server Authentication. But I am not seeing the Database anywhere in the SQL Server Management Studio. In Visual Studio, it goes to the localDb even though I have changed the connection string to have the db created in my SSMS.

<!--<add name="BookServiceContext" connectionString="Data Source=(localdb)\MSSQLLocalDB; Initial Catalog=BookServiceContext-20170328200803; Integrated Security=True; MultipleActiveResultSets=True; AttachDbFilename=|DataDirectory|BookServiceContext-20170328200803.mdf"
      providerName="System.Data.SqlClient" />-->
    <add name="BookServiceContext" connectionString="Data Source=AA5570LT;Initial Catalog=BookServiceContext;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

Sometimes, I can see the name BookServiceContext under Data Connections in VS Server Explorer, but when I click on the connection I get this error message

Cannot open database "BookServiceContext" requested by the login. The login failed. Login failed for user 'MLS\AAnwar'.

Visual Studio Server Explorer & SQL Server Object Explorer

What can I do so that it goes to the right connection in my SSMS?

Upvotes: 0

Views: 1153

Answers (2)

Truecolor
Truecolor

Reputation: 469

After working my head off with this issue for the past 2 days, finally have it working. All I had to do was set the Project as a "StartUp Project" in Solution Explorer and it fixed the issue. The database was created in the right Server with all the data.

Upvotes: 1

Alan Tsai
Alan Tsai

Reputation: 2525

VS Sql Server Object Explorer and the Server Explorer just provide a convient way of accessing the database
It provide similar functionality as SSMS - since you can log in to SSMS why not just manage there.

But if you really want to manage database through VS, then the Sql Server Object Explorer and the Server Explorer both provide a way of adding (toolbar there is add icon) any db connection, so you can do it there.

sql object explorer

server explorer

more info about the two view

The reason you see localdb in the SQL Server object explorer is because when you install VS, a localdb is installed, henc it will be shown by default As said above, if you want to connect to othere sql server, you can always add new sql server via tool bar

The Server Explorer will read your web.config to provide easy way of accessing the DB - by your error info, it seems that the current windows user do not have right to access db - it could be alot of reasons, seems you have mix login, why not just use sql server account? just change your connection string to:

<add name="BookServiceContext" connectionString="Data Source=AA5570LT;Initial Catalog=BookServiceContext;user id=sqlUserId;password=sqlUserPassword" providerName="System.Data.SqlClient" />

you can also manualy add a db connection via tool bar

Upvotes: 1

Related Questions