Tag: Database

Azure SQL Database Level Firewall Rules

If you have been using Azure SQL Servers and databases, you will already be aware that you need to configure the server level firewall. You may not know that you can also set firewall rules at database level too.
However this cannot be done through the Azure Portal. However both server and database level firewall rules can be easily managed using SQL.

Server Level

-- ========== SERVER LEVEL FIREWALL (master database connection)

-- List firewall rules
 SELECT * FROM sys.firewall_rules ORDER BY name;

 -- ADD Server firewall rule
 EXECUTE sp_set_firewall_rule @name = N'MyFirewallRule', @start_ip_address = '192.168.1.1', @end_ip_address = '192.168.1.10'

 -- DELETE Server firewall rule
 EXECUTE sp_delete_firewall_rule @name = N'MyFirewallRule'

Database Level

 -- ========== DATABASE LEVEL FIREWALL (specific database connection)

 -- List firewall rules
 SELECT * FROM sys.database_firewall_rules ORDER BY name;

 -- ADD Database firewall rule
 EXEC sp_set_database_firewall_rule @name = N'MyDBFirewallRule', @start_ip_address = '192.168.1.11', @end_ip_address = '192.168.1.11'

 -- DELETE Server firewall rule
 EXEC sp_delete_database_firewall_rule @name = N'MyDBFirewallRule'

See also

https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-configure-firewall-settings-tsql

Entity Framework – Setting integer keys client side gets ‘Cannot insert the value NULL’

I have just been banging my head against a wall trying to get a simple database record added to a simple table using Entity Framework! My table has a primary key field of type INT, but is NOT set as an identity field (i.e. Not set to increment SQL server side). I wanted the ability to set the Id in code.

An example would be a model like:

public Contact
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

then you would normally expect the standard EF code like this to work

var myNewContact = new Contact
{
    Id = 123,
    Name = "John Smith"
}
DataContext.Contacts.Add(myNewContact);
DataContext.SaveChanges();

However it always came back with the a DbUpdateException stating

“Cannot insert the value NULL into column ‘Id’, table ‘MyDatabase.dbo.Contacts’; column does not allow nulls. INSERT fails.”

It turns out that Entity Framework assumes that an integer [Key] field will always be generated server side. Maybe I should have known this, but actually found it very hard to locate this bit of information.

Once you know this the fix is very straight forward. You just need to decorate your model object with

[Key, DatabaseGenerated(DatabaseGeneratedOption.None)]

so the model above becomes:

public Contact
{
    [Key, DatabaseGenerated( DatabaseGeneratedOption.None )]
    public int Id { get; set; }
    public string Name { get; set; }
}

I hope this saves someone wasting as much time and loosing as much hair as I did on this simple issue!

One reason it took so long, is that I can’t get Entity Framework Profiler working with the Azure Emulator. If any has had any success with this, please let me know.