How to override Azure Sql connection in Azure portal

Question

I tried to override connectionstring in Azure portal with this connection :

Server=tcp:server.database.windows.net,1433;Database=mydatabase;User ID=username@server;Password=xxxxxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;

It gives me an error Keyword not supported: 'server' and when I tried it as:

metadata=res://*/DataAccess.MyDataBase.csdl|res://*/DataAccess.MyDataBase.ssdl|res://*/DataAccess.MyDataBase.msl;provider=System.Data.SqlClient;provider connection string="data source=tcp:server.database.windows.net,1433;initial catalog=MyDataBase;persist security info=True;user id=username@server;password=xxxxxx;MultipleActiveResultSets=True;App=EntityFramework""

It gives "Keyword not supported: 'metadata'. !!

So what is the exact connectionstring should I write ??

Also I tried all of connection string types as provided in the screenshot.

Answer 1

Ok I found an answer relative to my issue, but also I figured out something

First of all the connection string should be like

metadata=res://*/DataAccess.MyDataBase.csdl|res://*/DataAccess.MyDataBase.ssdl|res://*/DataAccess.MyDataBase.msl;provider=System.Data.SqlClient;provider connection string="Server=tcp:server.database.windows.net,1433;Database=MyDataBase;User ID=username@server;Password=xxxxxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

And notice the quotation should be " not " , Also as Nick said it should be Custom .

Answer 2

The first connection string ('Server=..') is used for connecting directly to the database, without passing through Entity Framework.
The second connection string ('metadata=..') is an Entity Framework connection string. If you're using EF, you should mark the connection string in the Azure Management portal as 'Custom' as opposed to 'SQL Database'.

Answer 3

In the control panel, there is a spot on the Database Panel that says "Show database connection strings" that gives you complete connection strings for your database for ADO.Net, PHP, Java and ODBC. These can be used as a starting point, from which I added MARS support and a different user/password to mine.

Here is my connection string to help you out. We use this one to override in the Azure WebApps Control Panel.

Server=tcp:[AZURE_DB_NAME].database.windows.net,1433;Database=[DATABASE_NAME];User ID=[USER_ID];Password=[PASSWORD];Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;MultipleActiveResultSets=True

As to the Drop Down, we have it set to SQL Database.

One thing that comes to mind. Have you tried using your connection string in VS Debug mode to see what happens? You just have to configure your AzureDB to allow a remote connection from your IP for that to work. It could provide some insight for you that your connection is correct but something else is failing.

Answer 4

I tried solution from Mohamed Farrag but got this error:

System.ArgumentException: Keyword not supported: '.ssdl|res://*/dataaccess.MyDataBase.msl;provider'.

After removing DataAccess. from my connection string it worked:

metadata=res://*/MyDataBase.csdl|res://*/MyDataBase.ssdl|res://*/MyDataBase.msl;provider=System.Data.SqlClient;provider connection string="Server=tcp:server.database.windows.net,1433;Database=MyDataBase;User ID=username@server;Password=xxxxxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"