Some thoughts of your typical data shepherd / data plumber / data dance teacher sort of person.
Since this one caught me out and a few other people it seemed like a good idea to blog about this.
Sometime ago the Azure portal got a make over, which l have say make the portal look very pretty. Whilst setting up a proof of concept for PowerBI l thought it was also a good time to look at setting up an Azure SQL database. The last time l did this was in the previous portal. So l duly set up the database, one of the last steps was to allow the IP address from my work organisation access to the database. So l goggled and found this page - https://azure.microsoft.com/en-gb/documentation/articles/sql-database-configure-firewall-settings/
The Wrong Way
Open the new style Azure Portal
Click on browse (1)
From the list of blades click once on SQL databases (Figure 1)
Figure 1 - Opening the SQL databases blade
Click on the 'SQL databases' blade this will open a list of the all the SQL databases that are in your account (Figure 2)
Figure 2 - List of SQL databases showing area where to click to show settings
Clicking on the ellipsis will cause a new menu window to appear (Figure 3)
Figure 3 - Clicking on the ellipsis to get the settings link
Click once on the settings link this will then open the settings window (Figure 4)
Open the new style Azure Portal
Click on browse (1)
From the list of blades click once on SQL servers (Figure 5)
Figure 5 - Opening the SQL servers blade
Click on the SQL Servers blade and this will open a list of all the SQL servers in your account (Figure 6)
Figure 6 - List of SQL servers
Move the mouse over the row which is the SQL server that require to see the settings for. Click once with left hand mouse button the selected SQL server in Figure 6 this is SQL server 'kjsgbci03i'. This will then open Settings window on the far right hand side (see Figure 7)
Figure 7 - Settings page for SQL server
Click once on the firewall row and this will open the 'Firewall settings' window (see Figure 8)
Figure 8 - Firewall settings window
Splitting the settings for SQL servers and SQL databases, does make sense, the same logic is used within SSMS. That said in the old style portal, the link to the page for setting allowed IP Addresses is under 'SQL databases' (see Figure 9). Hence why l must have looked at SQL databases blade first. At least that's my excuse and l am sticking to it ;->
Figure 9 - SQL server page with link to Manage allowed IP addresses (Firewall rules) in the old style Auzure portal.
Over the years l have worked with many different people, and observed many people at work. When working in a mechanical garage l was impressed with the range of tools used by the time served mechanic's. When a plumber and electrician come to fit a new kitchen l was always was curious about the range to tools to do different jobs.
Today l had a relatively simple task when completing data migration task, which l was doing using SSIS. Most of the work uses TSQL when possible. In this case though l was asked convert the a string value from the source for one specific control on one form to Camel Case. First thought use play to my strengths and use TQL, so quick google, and found this stack overflow posting -> http://stackoverflow.com/questions/5164201/is-there-any-sql-server-built-in-function-to-convert-string-in-camel-case
Ok yes it was possible to use TSQL, the phrase that ran through my mind at that point was "when you have a hammer everything looks like a nail". Yes l could create the function and call it from the query etc.... SSIS is the most wonderful toolbox though (pun intended if you notice it). One of them is the script task. Since l only had to convert one control (there is a large number!) why not use some code? Would that be easier? Goggling lead to stack overflow posting -> http://stackoverflow.com/questions/1206019/converting-string-to-title-case-in-c-sharp ( This article filled in the gaps for me https://msdn.microsoft.com/en-us/library/system.globalization.textinfo.totitlecase.aspx)
public void Main()
string fieldName = (string)Dts.Variables["User::str_Field_ID"].Value;
string stringValue = (string)Dts.Variables["User::str_NNLFP_VALUE_TXT"].Value;
if (fieldName == "XYZ014")
TextInfo textInfo = new CultureInfo("en-GB", false).TextInfo;
stringValue = textInfo.ToTitleCase(stringValue);
Dts.Variables["User::str_NNLFP_VALUE_TXT"].Value = stringValue.ToString();
Dts.TaskResult = (int)ScriptResults.Success;
Yes it has taken me time and effort to learn C#, that said it's an excellent additional to my toolbox. Yes l could have done the same in TSQL, it would have taken probably taken longer. Right tool for the right job? That l am sure is a matter of opinion and or debate. Speaking personally for this specific project yes l believe so. It has encouraged me to keep adding to my own personal IT toolbox......
Ok long story, short. I downloaded and installed SQL server 2016 CTP3 on Windows 10 Pro X64 Virtual machine which was set up for testing purposes. Yes l know that was a silly idea, what the heck living dangerously is fun sometimes! The OS was a standard install and it's standalone as in not connected to a domain or clever stuff. When propmted to enter a user account, l used a personal Microsoft account l have. Next l installed SQL Server 2016 CTP nothing fancy and choose the native SSRS install
(If you just interested in list of steps l followed they are at the bottom of this posting)
Next logged in as the user account that was used during set up, this is Microsoft account. Now l wanted to play with SSRS, so open the default browser (Microsoft Edge) and entered the url “localhost/reportserver” and waited. Next got a dialog box asking me to enter my user credentials
So l entered my Microsoft account details, and eventually ended up with the error message below.
Yes l know that I should have remembered, an admin account requires to grant permission on Report Manager to the account you are connecting with “Doh!”. Next step was to see if l could run Microsoft Edge (the default browser) as Administrator, no that was not possible. As can be seen from the screen shot below it was possible to run command prompt as Administrator, which did not make sense, but not Microsoft Edge.
Again Google the rescue and found this page - http://www.virtualizationhowto.com/2015/07/windows-10-edge-opened-builtin-administrator-account/. Followed instructions open Microsoft Edge, restarted Windows was able to run Microsoft Edge as Administrator, "Yippee!!!" Typed in the url “localhost/reportserver”, then eventually got the following error message.
During earlier search found this page http://www.windowscentral.com/how-find-internet-explorer-windows-10-if-you-really-need-it. I had tried this before making the change in this page http://www.ghacks.net/2014/11/12/how-to-enable-the-hidden-windows-10-administrator-account/. So entered ‘Internet ‘ into the search box and selected to run Internet Explorer as Administrator.
Success !!!!!! (see screenshot below) As the saying goes “ a long way for a short cut”, however it works!
So l set about making the relevant changes in report manager, setting up the Microsoft user account l normally log in with as content mgr etc. That all seemed to go as expected. Next switched accounts back to the Microsoft account and thought l will just open Microsoft Edge. Since the account has been set up and l do not need to run it as Administrator. This did not work l got the error message below. So l typed into the search box 'Internet' and Internet Explorer was one of the applications l was able choose to run, the result can be seen below. It worked as expected.
Is there an easier way? Then please let me know, this was not the most fun learning journey, and I’m always open to learn.
So what steps did l follow?
Used instructions to enable the administrator account and set the password for the Administrator account.
Switched accounts from Microsoft account to Administrator account
Used these instructions to make relevant change to the security policy
Used these instructions to find Internet Explorer and open using "Run as Administrator" option
4) Open SSRS report manager (running as local administrator), set the relevant permissions for the Microsoft account
5) Switched accounts from Administrator account, to Microsoft account. Searched for Internet Explorer then browsed to "localhost/reports" and was able to see SSRS report manager.