Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to connect sql server express database to VS 2017

I keep running into issues just trying to make a connection from my c# program in Visual Studio 2017 Express to a small local database with one table I created in Sql Server Express. I would like to connect it as a data source within Entity Framework in my solution (which is in c#). I've searched MSDN and this site for days but haven't found a way to solve this.

The issue now is that when I go through the Entity Data Model Wizard (existing database) and go to establish a New Connection, select "Microsoft SQL Server Database File (SqlClient)" and browse to my database -- the Test Connection generates the error:

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\MyDatabase.mdf". Operating system error 5: "5(Access is denied.)". An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\MyDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I'm running Visual Studio as Administrator (this got past a prior issue that wouldn't even let me browse to the database without prompting an error message that I didn't have permission to open the database file).

I can view my database, its table, and its data in the SQL Server Object Explorer inside Visual Studio - regardless of whether I'm running it as Administrator or not. Not sure if that is significant. I'm new to all this, so sorry if I am missing something obvious.

like image 420
Gus Yorke Avatar asked Mar 25 '17 04:03

Gus Yorke


1 Answers

Figured out my issue: in Visual Studio's "Add New Data Source" wizard, the option I kept choosing was "Microsoft SQL Server Database File", because its description is "Use this selection to attach a database file to a local Microsoft SQL Server instance (including Microsoft SQL Express) using the .NET Framework Data Provider for SQL Server."

However, and I'm not sure why, this was not the right selection.

I tried selecting "other" instead.

Then on the next page input ".\SQLExpress" as the Server Name (mentioned on connectionstrings.com/sql-server/ ...Thanks for the lead @ryguy72!)

Then, under "Connect to a database" my local list of databases popped up, including the one I had created already using SSMS.

Test Connection finally worked then!

like image 187
Gus Yorke Avatar answered Oct 03 '22 06:10

Gus Yorke