Azure, Sql User invalid from Azure Website

18 January 2015 brianbruff Azure

Problem

I was faced with a problem this morning that took me a good 30 minutes to figure out..

I had created a website and associated SQL database. However I changed said database as part of some development work. The problem was that even though my publish profile was overriding the Release Connection String with my new database it was getting ignored!

image

 

I knew that the connection string I was supplying was correct as I could log in with Visual Studio and SSMS.

Cause:

The reason is that the website had already an connection string (under the Configure tab) and this was taking preference. The reason this is here is that one does not have to store the Azure connection string in the publish profile which is quite nice, same goes for a lot of other Azure features.

image

 

Solution

I removed same and then it works. (Fixing it is also another option but this code is in a private git repository so it’s not a concern for me just now).

SQL Server Check Constraints

20 December 2010 brianbruff

Here's an easy one to get caught out on.

Say you remove a constraint on a table to do some maintainance ect.

e.g.

ALTER TABLE BANDWIDTH NOCHECK CONSTRAINT FKUserLimits;

When you wish to bring the constraint back on stream you may be surprised to find the following does not quite work...

 

ALTER TABLE BANDWIDTH CHECK CONSTRAINT FKUserLimits;

You may be be able to see this in the execution plan on SSMS if the constraint was used in query optimization.

but you can check for sure by executing the following

select name, is_not_trusted FROM sys.foreign_keys where name = 'FKUserLimits';

you'll find that is_not_trusted is 1, indicating that the constraint is not trusted, this is because someone could have modified the table while the constaint was turned off, the sql to reenable the constraint needs to be told to check it while doing so..

here's how

 

ALTER TABLE BANDWIDTH
WITH CHECK
CHECK CONSTRAINT FKUserLimits;

This option tells SQL server to verify that all rows in the table comply with the constraint prior to turning it back on. If any rows do not comply with the constraint, an error message is returned and the alter table statement is rolled back.