Thursday, May 11, 2017

Creating Users in Azure SQL

I have been working quite a lot in Azure recently and have done some work with SQL Azure PaaS databases.

I am not an SQL DBA by any stretch of the imagination, so some tasks have taken a bit of Googling to conquer!

There are some key differences between a full SQL install and Azure SQL.  One of the main differences with Azure SQL Azure PaaS databases is that you need to do so much more through Transact-SQL.  My limited experience of SQL has all been based through GUI prompts, so this initially proved a problem for me.

One of the tasks which I have had to complete a fair amount recently is to create database users.  In the Azure Portal you can create administrator accounts for the whole server, but this doesn't match the requirements of the business in most occasions.

I came across these blog posts on the Microsoft website

https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

So to create a read-only user for a single database, the Transact-SQL you need are

USE [master]
GO
CREATE LOGIN readonlyuser WITH password='supersecurepassword1';
USE [specificDB]
GO
CREATE USER readonlyuser FROM LOGIN readonlyuser;
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';

Now....when a user tries to connect with this account, they will most likely get this error.


The reason this error occurs is because you haven't added the user to the master database.  The trouble is that if you add the user to the master database, they will get Guest access and be able to see all of the databases on the server.  They will not be able to access the contents, but they will be able see their presence.

If you are not fussed by this, you can run the following Transact-SQL to solve your issue.


USE [master]
GO
CREATE USER readonlyuser FROM LOGIN readonlyuser;

Alternatively, you can inform the user to type in the specific database in the options of SQL Studio



Now, all of you SQL on-prem DBAs are probably yelling at the screen "use the default_database" T-SQL.  Well, guess what?  It isn't supported in Azure (yes, this is mentioned in the first link on the page).


I hope this is useful!

Sam

Sunday, October 23, 2016

Authentication vserver cannot be bound to a CS server if VPN server is already bound to it

The title is a bit of a mouth full!  Basically with NetScaler 11 onwards, Content Switching is now supported for both web applications and NetScaler Gateway.  This is great news which will allow people to conserve more services behind less IP addresses.

I wanted to test this out for myself, so I span up a NetScaler in my lab and started the configuration.  I wanted to enable AAA so that I could pre-authenticate requests into my web applications (Outlook Web Access in my lab).  The below picture shows an overview (check out the Citrix article it is linked from!)

http://support.citrix.com/article/CTX201949

Firstly I completed the NetScaler Gateway Wizard and ensured that I could authenticate and launch desktops from my XD7.11 lab.

Next I followed Dave Bretty's blog to Content Switch the NetScaler Gateway VS and a newly created VS for OWA.

Then I created an Authentication VS, then created a policy which say any requests to the AAA address would go to the authentication VS.  Lastly I went to bind the policy to my Content Switch VS.  I received the titled error message.


This left me scratching my head for a while.  The VPN server that is mentioned is the NetScaler Gateway VS.  Then it struck me, the NetScaler Gateway is completing pre-authentication, I should just be able to use this VS.

I went into the LB VS for OWA and under authentication I chose Form Based Authentication, Authentication FQDN needs to be the NS gateway address.  Lastly ensure that the NetScaler Gateway VS is the one that is being used for NS gateway.


After saving this, when trying to go to email URL (email.domain.com) the NetScaler should redirect this to the NS Gateway URL (xendesktop.domain.com).  After successfully authenticating, the NetScaler should redirect to the email URL (email.domain.com) and if you have IWA enabled on your exchange server, you should be presented with your inbox!

Sam

Friday, October 21, 2016

Using PowerShell to update Visio diagrams

Being in IT or architecture generally means using Visio, FACT!

I have been completing lots of work in Visio recently as part of document migration into a Enterprise Architecture tool called iServer.  This required me to dust off my knowledge of VBA and creating lots of macros.  I am not a massive fan of macros, they seem really archaic.

In the more recently iterations of these scripts, I realised that I could use PowerShell to complete the same tasks.  PowerShell is something I am far more comfortable with and because it sits outside of the application itself, there is more opportunity to run scripts against a larger set of files.

Rather than bore you with some of the scripts I created for my business process diagrams, the below example is a script which you can use to update IT related documents.

Below I have a rack diagram I created in Visio.  It has four servers and one router with their names added to ShapeData and a Data Graphic which shows this as a bubble.

Before


Now, wouldn't be cool if you could update all of your documents with IP address without having to sit there typing it in?  Well that is what I did!


This is the result

After
Here is the script which completes this task

#Ask the user which folder the files are stored
$location = read-host "Where are the files stored"
#Get all visio files in the location specifed
$files = get-childitem $location | where {$_.Name -match ".vsd"}

#loop that will go through each visio file in the folder
ForEach ($File in $Files){

#create Visio com object
$visio = New-Object -comobject Visio.Application

#open next file in list
$Visio.Documents.Open($file.fullname)

#loop that will go through each shape on the page
ForEach ($vsoShape In $visio.application.activepage.shapes) {

#get master, if Server or Router, then do an NSlookup of the name and apply the resultant IP address to the shape in question
$master = $vsoShape | select -expand Master 
If($master.name -eq "Server" -or $master.name -eq "Router 1")
{

$networkname = $vsoshape.cells("Prop.NetworkName").ResultStr(0)
$ipaddress = Resolve-DnsName $networkname | select IPaddress | foreach {$_.IPaddress}
$vsoCell = $vsoShape.Cells("Prop.IPaddress")
$vsoCell.formula = """$ipaddress""" 

}
}

#save the document
$visio.Application.ActiveDocument.Save()
#quit visio afterwards
$visio.Application.Quit()

}

You can see how this script could be edited to complete any manner of tasks.  If the information was in the Shape text itself you could use this

$characters = $vsoShape | select -Expand Characters
$char = $characters.textasstring

To see what else you can do programmatically with Visio, please see the MSDN page.

Got any tedious Visio task that you could do with automating?  Let me know in the comments.