SQL Azure connectionstring issues

Hello-

I've run out of db space on our godaddy site, so I set up a SQL Azure database server and deployed my database to it. I then added my website's IP address to the Azure allowed IP list.

The problem is that I cannot get my site to successfully connect to the Azure database.

When I change the settings.txt to use the recommended connectionstring:

Server=tcp:myserver.database.windows.net,1433;Database=myuname;User ID=my@ID;Password={your_password_here};Trusted_Connection=False;Encrypt=True;Connection Timeout=30;

I get an ASP.NET error saying that the keyword 'Server' is not supported...

When I try this one:

Provider=SQLNCLI11;Password=myPassword;User ID=[username]@[servername];
Initial Catalog=databasename;Data Source=tcp:[servername].database.windows.net;

or this one:

Provider=SQLNCLI10;Password=myPassword;User ID=[username]@[servername];
Initial Catalog=databasename;Data Source=tcp:[servername].database.windows.net;

I get an ASP.NET error saying that the keyword 'Provider' is not supported...

When I try this one:

Driver={SQL Server Native Client 10.0};
Server=tcp:[serverName].database.windows.net;Database=myDataBase;
Uid=[LoginForDb]@[serverName];Pwd=myPassword;Encrypt=yes;

I get an ASP.NET error saying that the keyword 'Driver' is not supported...

The only one that gets different results is this:

DataProvider: sqlserver
DataConnectionString: Data Source=tcp:myserver.database.windows.net,1433;Initial Catalog=databasename;Integrated Security=False;User ID=my@id;Password=mypassword;Connect Timeout=30;Encrypt=True

BUT....I get a 'no database instance' error on that....


A few times, I got this error:

the argument 'nameorconnectionstring' cannot be null, empty or contain only white space.

But I found another post where they recommended commenting out the layout reference in the /Views/_ViewStart.cshtml, and since doing that, I haven't gotten this error...but still always the 'no database instance' error...

Anyone else have this problem?
I need to solve it ASAP because we won't be able to squeeze any new customers or orders into the database pretty soon!

Steve

have checked the security settings in your azure sql?
You have to grant access between your web and your DB.

cheers, richard

UPDATE:

Despite the error only saying "no database instance", and the fact that SQL Azure monitor indicated that no successful or failed connections have been made, it seems like it's a security issue.

I've deleted my db logins and users and recreated them via T-SQL scripts, and set the new user in the db_owner role.
Still no joy.

I created a new user and set him as a database owner as well...adjusted my settings.txt.. same thing...no cigar.

Any suggestions would be welcome at this point...

Thanks,
Steve

this syntax is working for me on Azure:



DataProvider: sqlserver
DataConnectionString: Data Source=xxxx.database.windows.net;Initial Catalog=xxxxxxxxxxxxxx;Integrated Security=False;Persist Security Info=False;User ID=xxxxxx;Password=xxxxxx

Ya, thanks...it should be working for me too, but it just isn't.

In your example, you don't have the "tcp:" preceding the server name..I've tried with and without it and have same results.

I found my problem was related to my database size and my quota being full.

In a moment of frustration I decided to clean up and start again and instead of deleting the database from SQL Server Management Studio I logged into Azure. Lo and behold my dashboard said my database quota was over the limit. I upgrade a couple of levels from the free offering until I was back in the green and it all worked.

This resolves the problem. Missing database instance is mostly due to azure db size issue.

marcwagener wrote:

Thank you man you save my many hours when I am trying to deploy Nop into Azure.this works for me in single attempt.Thanks a lot again.