Press "Enter" to skip to content

George Woods Posts

Overview of Connecting to Azure Analysis Services

With Azure Analysis Services Firewall off.

As admin account – Log in to Azure

Deploy model

Connect with SQL Server Management Studio from laptop

With admin account – 3 options for connecting

Does not work

Does not work

Using MFA does work – screen pops up for password and then another for text message before gaining access. – we can see the model as well, since this account is an Analysis Services Admin

As my non-admin account same three options for connecting

Does not work

Works but am just typing password in

Works but connects just asking for password, not asking for mfa, however does not have access to any databases as they are not marked as an admin. They will need a role assigned


Connect from Power BI

Using non-admin account

Using admin account – asks for password and MFA text

As admin create a read role in the model

Add non admin to read role

Try again to connect from Power BI using non admin account

Connection works with no MFA

 

 

 

Leave a Comment

Geo-Replication in Azure

I was recently asked to look at creating a read-only copy of a SQL Server database in China, with the source being in the UK. . For on-premise, to get AlwaysOnAvailabilty with the replica being a read only copy we would need to be running Windows FailOver Clustering Server version and the database would need to be Enterprise Edition. However, this is easily set up in Azure, using Geo Replication and copies would only be seconds apart in replication.

I decided to do some testing. Geo replication is available at all price points in Azure, so for testing, I spun up a database in the Basic Pricing tier in the West US region. Once the database was available, I created a simple table with 4 rows of data and then confirmed the data existed. The next step was to go back in to Azure and under the newly created database resource, I clicked Geo-Replication. This opened another window that allowed me to choose a location. The picture above, shows all of the available locations. The blue hexagon is your primary database and the green hexagons are the available replication locations. For this one, I chose East Asia. The cost for this Geo-Replicated readable copy is the same as the primary copy. So in my case, each database was $5 per month.

 

The Geo-Replicated database will have the same database instance name, but will have a different server name.

As part of creating the new readable database, Azure will copy all of the data that is in the primary to the readable. Since I didn’t have much data, this process only took a minute or so. Once I got the notification that the readable was created, I connected via SSMS and was able to see the data. I then did some basic testing of deletes, inserts and updates. Obviously, the amount of data will make a difference, but as for speed, as soon as I hit execute in the primary database window and refreshed my select on the readable copy, the changes were there.

If you are looking for a read only copy of a SQL Server database, I definitely recommend Azure Geo-Replication. The whole process took less than 10 minutes to set up.

 

 

Leave a Comment

Creating a RunAsAccount in Azure for Azure Automation

Warning: We have three different people with three different levels of security in our Azure tenant and only one of us could create this account.

Person 1: Is the owner of a Resource group and can create items in the resource group, including the Automation Account and Runbooks, but can not create the RunAsAccount

Person 2: Is the tenant admin and can do what Person 1 can do and a lot more, but does not have permissions to create a user in Azure AD, so can not create the RunAsAccount either.

Person 3: Has all of the permissions as person 2, but is also able to create users in Azure AD, so he was the only one that was able to create the RunAsAccount.

Below are the steps that he followed to create the account

1. In the Azure portal, click All resources. In the list of resources, select the Automation account from the list of Automation accounts.

2. In the left-hand pane, select Run As Accounts under the section Account Settings.

3. Select Azure Run As Account. After selecting the Add Azure Run As Account, a pane appears and after reviewing the overview information, click Create to proceed with Run As account creation.

4. While Azure creates the Run As account, you can track the progress under Notifications from the menu. A banner is also displayed stating the account is being created. This process can take a few minutes to complete.

Since we only wanted the RunAsAccount to have permissions to certain Resource groups and not the whole subscription, we followed the steps below.

Limiting Run As account permissions – To restrict what the RunAs service principal can do, you can remove the account from the contributor role to the subscription and add it as a contributor to the resource groups you want to specify.

1. In the Azure portal, select Subscriptions and choose the subscription of your Automation Account. Select Access control (IAM) and search for the service principal for your Automation Account (it looks like _unique identifier). Select the account and click Remove to remove it from the subscription.

2. To add the service principal to a resource group, select the resource group in the Azure portal and select Access control (IAM). Select Add, this opens the Add permissions page. For Role, select Contributor. In the Select text box type in the name of the service principal for your Run As account, and select it from the list. Click Save to save the changes. Do this for the resources groups you want to give your Azure Automation Run As service principal access to.

 

 

 

Leave a Comment

Loading Linux command line logs in to Windows SQL Server

We recently decided, for auditing reasons, that we would like to keep a record of all of the commands that were run on our linux machines and because of separation of duties, we would load those logs in to a SQL Server instance on Windows. Funny how I feel the need to avoid confusion and make sure I say were the SQL Server instance is.

1. First, to be able to get the files I needed to be able to ssh to the linux servers. The easiest way to do this was using Putty

I have separated out the two scripts. First one being the script to download the logs to Windows server and the second script is to load logs to SQL Server

2. Download logs to Windows Server

Since I am connecting to numerous linux servers, I am using a file to store the user names, passwords and server names and will iterate through each line in the file to connect and get the logs.

The files manager.txt and others.txt only contain the password used by that user.

Example server name file

 3. Script to get server names, user names and passwords

 

4. Create SQL Server table with the following columns – filename, command, workingDirectory, server, username and date

5. Load logs in to SQL Server

Example log file from Linux – As you can see, all of the commands came in as comma separated, but they were all on one line.

 

Using the Get-Content command changed the file to a usable format, we used the replace command to remove the space and the command number and we used Set-Content command to save this as a txt file

 

6. SQL Server table

 

 

 

Leave a Comment

SQL Server Reporting Services Timeout

The other day we ran in to an issue where the report was running 30 minutes, this may be the only report that we have that runs this long, but then it fails. The only error message was “Report processing has been canceled by the user. (rsProcessingAborted), but after checking, no one canceled the report processing. What just happened?

It was determined that the Report timeout option was set to “Use the default setting” like most of our reports are.

 

Well, where is this default setting and what is it set to? The default settings timeout is set in the site settings page of your SQL Services Reporting Services and it was set to 1800 seconds or as you guessed, 30 minutes.

 

You have two options to fix the the timeout issue.

1. Change the default settings timeout for the whole site in the site settings page.

2. Change the Report timeout setting just for that report. This is done by going to the report, clicking the … and choosing manage.

You then scroll down to Advanced and either choose “Allow the report to run for (add amount of seconds here) seconds before timing out” and changing the seconds to a higher value that will allow the report to run or choose the “Allow the report to run indefinitely (no timeout)” option.

 

Click Apply and your report should not complete.

 

 

 

Leave a Comment

FindTime for meetings

I received an email today from FindTime Notifications. If you haven’t heard of this Outlook add-in, it’s a pretty cool addition from Microsoft, that allows you to ask people outside of your company to vote on times that they would be available to meet. You can mark a time slot as preferred, yes, or no. You can also suggest other time slots. Next time you need to schedule a meeting, take a look at https://findtime.microsoft.com/

 

 

 

 

 

 

Leave a Comment

Using alias to upgrade old servers

One trick that I have used numerous times and really like is to use a server alias when moving to new servers. For instance, we were moving numerous databases from a 2000 server named serversql0001. Since this server was so old, we had no real idea how many places or connections people may have used the alias, so we decided during the downtime to remove this alias and point it to the new server that we were moving the databases to. After we brought the new server up, we checked some of the software that was connecting to the databases, and as expected, everything was fine, with no need for connection changes.

Leave a Comment

Connect to SQL Server when you don’t know SA account or don’t have sysadmin access

Below are the steps you need to perform to grant SYSADMIN access to a user in SQL Server in case you are completely locked out.

1. Download PSexec to connect using SQL Server Management Studio using the NT Authority\System

Download PsTools from https://download.sysinternals.com/files/PSTools.zip
Unzip the content and copy PsExec.exe to C:\Windows\System32

2. Stop the SQL Server and SQL Server Agent services on the server.

3. Open a cmd prompt window as administrator and navigate to SQL Server’s Binn directory. You may need to adjust your path based on your install location.

ex. C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Binn

4. Once you are in SQL Server’s Binn directory run the ‘sqlservr -m’ command to start SQL Server in single user mode as shown below. Had to add the location of the ERRORLOG

sqlservr -m -e C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Log\ERRORLOG;

If it’s a named instance:
sqlservr -m -s <instancename> -e C:\Program Files\Microsoft SQL Server\MSSQL11.SERVICECORE\MSSQL\Log\ERRORLOG;

After the SQL Server instance was started in single user mode, I was receiving logon errors form other users that were trying to logon from the application, when it was restarted in single user mode. I ignored the errors and moved on.

5. Execute PsExec – may need to adjust the ” marks and file locations

run cmd as administrator

PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”

The above command will launch SQL Server Management Studio and gives you a “Connect to Server” window and the User Name will be pre-populated with NT AUTHORITY\SYSTEM

6. Click Connect and then go in to Security > Logins and add your account as a sysadmin

7. restart the sql server services

 

 

 

Leave a Comment

Reading List – 2018

I don’t normally do New Year Resolutions, but during the Christmas break I was reading numerous articles on LinkedIn, I’ve always enjoyed Linkedin, with it’s career connections and articles. However, I also realized how much wasted time I spent on Facebook and Twitter, so because it’s that time of year, I am calling this my “New Year’s Resolution”: Spend more time bettering myself and my career

First up, purchase a kindle and I got a great deal. I wasn’t looking for two kindles, but when I saw Amazon had a special deal, buy two kindles get $60 off, I couldn’t pass the opportunity to gain some bonus points with my wife. Her kindle is a few years old and only 6gb, so I was able to get two Kindle Fire HD 8’s for $99.

Create a list of books to read and afterwards write a review. I am sure this list will continue to grow, but for starters and in no particular order:

Hit Refresh, Microsoft CEO, Satya Nadella 

Business Adventures, John Brooks  

Mindset, Carol Dweck 

The Better Angels of Our Nature, Steven Pinker 

The Innovators, Walter Isaccson 

Hackers: Heroes of the Computer Revolution, Steven Levy 

The Four: The Hidden DNA of Amazon, Apple, Facebook, and Google, Scott Galloway

Not a book, but… Oracle E-Business Suite Architecture

Leave any book recommendations in the comments.

 

Leave a Comment

Determine what Linux Version

4 different commands that you can run to see information about your Linux distro.

[oracle@server ~]$ cat /etc/issue
Oracle Linux Server release 6.8

[oracle@server ~]$ cat /etc/*release
LSB_VERSION=base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Oracle Linux Server release 6.8
NAME=”Oracle Linux Server”
VERSION=”6.8″
ID=”ol”
VERSION_ID=”6.8″
PRETTY_NAME=”Oracle Linux Server 6.8″
ANSI_COLOR=”0;31″
CPE_NAME=”cpe:/o:oracle:linux:6:8:server”
HOME_URL=”https://linux.oracle.com/”
BUG_REPORT_URL=”https://bugzilla.oracle.com/”

ORACLE_BUGZILLA_PRODUCT=”Oracle Linux 6″
ORACLE_BUGZILLA_PRODUCT_VERSION=6.8
ORACLE_SUPPORT_PRODUCT=”Oracle Linux”
ORACLE_SUPPORT_PRODUCT_VERSION=6.8
Red Hat Enterprise Linux Server release 6.8 (Santiago)
Oracle Linux Server release 6.8

Kernel Version commands
[oracle@server ~]$ uname -r
4.1.12-103.9.6.el6uek.x86_64

**Whether you are using 32 bit or 64 bit version… the x86_64 means a 64 bit kernel

[oracle@server ~]$ uname -a
Linux server 4.1.12-103.9.6.el6uek.x86_64 #2 SMP Wed Nov 15 18:03:27 PST 2017 x86_64 x86_64 x86_64 GNU/Linux

Leave a Comment