MS SQL database as a service connection string for C#, MVC Entity Framework application

Question

I have a MS SQL database in azure (database as a service). I want to connect to this database from my C# MVC application using Entity framework. I want to know what should be the connection string. Following is the connection string which I used to connect to my local database.

<add name="PortalDbContext" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;User Id=sa;Password=sa;Initial Catalog=DemoDB;" />

Thanks in advance

EDIT

<add name="PortalDbContext" providerName="System.Data.SqlClient" connectionString="server=dbserver.database.windows.net:1433;User [email protected];Password=sa;Initial Catalog=DemoDB;" />

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid

<add name="PortalDbContext" providerName="System.Data.SqlClient" connectionString="server=dbserver.database.windows.net;User [email protected];Password=sa;Initial Catalog=DemoDB;" />

Login failed for user 'sa'.

<add name="PortalDbContext" providerName="System.Data.SqlClient" connectionString="Data Source=tcp:dbserver.database.windows.net,1433;Initial Catalog=DemoDB;Persist Security Info=False;User [email protected];Password=sa;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" />

Login failed for user 'sa'.

Did you look in the Azure portal? If I remember right, it will tell you the connection string. — mason
– mason, Commented Sep 11, 2017 at 14:36
What do you mean "did not work"? Did you find the connection string? Did you use it in your application? Did you get an error? What was the error? Did you configure your network to accept the connection? You need to be a lot more specific than "did not work". — mason
– mason, Commented Sep 11, 2017 at 14:40
@mason, Sorry, I have edited the post. I have used it in my C# application — user1396423
– user1396423, Commented Sep 11, 2017 at 15:08
You have to modify the firewall to allow connections to the database from outside. — mason
– mason, Commented Sep 11, 2017 at 15:16

rickvdbosch · Accepted Answer · 2017-09-11 15:38:25Z

Have a look in the Azure portal: on the Overview blade of your SQL Database, there's a 'Show connection strings' link that shows you the right connection strings.

The default connectionstring is:

Data Source=tcp:<SERVER_NAME>.database.windows.net,1433;Initial Catalog=<DATABASE_NAME>;Persist Security Info=False;User ID=<USER_NAME>@<SERVER_NAME>;Password=<PASSWORD>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

EDIT (because of OP's edit)
I have two small additions:

User sa is not allowed in Azure SQL as you can see in the image below. Please make sure you have the correct username and password for the instance. You can find the username by going to the SQL Server your database is on in the Portal and looking at the Server admin in the top right corner of the Essentials pane.
By default the SQL Server Firewall does not allow external IP addresses to connect to the SQL Server instance. You can use the 'Set server firewall' link in the first screenshot above to allow your IP address to connect to the SQL Server.

Collectives™ on Stack Overflow

MS SQL database as a service connection string for C#, MVC Entity Framework application

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related