CODEWITHSUNDEEP
c#sql-serverazureconnection-string
user1396423
user1396423

Reputation: 263

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

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'.

Upvotes: 1

Views: 599

Answers (1)

rickvdbosch
rickvdbosch

Reputation: 15551

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.

Connection strings in the Azure portal

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:

  1. 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. Username sa not accepted

  2. 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.

Upvotes: 1

Related Questions