Signing Into SSMS 2014 as Domain User on Non-Domain Computer

Overview:  Just a quick tip about one way to run an application, such as SQL Server Management Studio, under the context of a domain user if you're not on a domain computer. 

Last week I started a new client engagement and we were tackling connectivity and setup.  For this engagement our team isn't working on VMs or client-provided equipment; we're using our own equipment owned by BlueGranite (my employer) rather than the problem. Ok.

However, my SQL Server credentials are Windows-based. Except that I'm not on the domain of the client. So I found myself trying to run SSMS as another user (the shift-right click thing) and it wasn't working.

The solution is the RunAs command with the /NetOnly switch.  RunAs executes a program under a different user account than the current login. When we add in the /NetOnly switch, it specifies the RunAs will pertain to remote access only -- meaning any connections made to other remote computers (like when we connect to a database in SSMS) will be made with the new user specified.

Here's the syntax for launching SSMS for SQL Server 2014 (with a default install location):

runas /netonly /user:domainname\username "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\ssms.exe"

When you hit enter, you'll be prompted for the password associated with the domain user's account.

After you provide the password, it'll launch the application. Even though the SSMS login window doesn't indicate it, your remote connections will be made using the domain user identity rather than the local user.


If you'll be using this technique a lot, you probably want to create a custom shortcut. I went old school - typed the command into a text editor, saved it as a .bat file, and stuck it on my desktop. 

The nice thing is you can do this with a ton of programs. This technique can be used for testing or even performing a quick task with an elevated security account.

Third Annual SQL Saturday BI Edition in Charlotte

The third annual SQL Saturday, BI Edition, is back in Charlotte! It's coming up next weekend, on Saturday, October 4th.  

SQL Saturday is a free* training event for Database and Business Intelligence professionals. It's a conference condensed into a single day. The event includes training sessions led by local, regional, and national speakers, and lots of opportunities for networking and meeting up with peers. There's also exhibitor tables with useful products, information, and job opportunities.

The schedule has 40 technical sessions, at varying levels, on:

  • Data Analytics
  • Cloud
  • Big Data
  • Information Management
  • Development
  • Information Delivery
  • Environment Architecture
  • System Administration
  • Professional Development

More information: 



I hope to you see you there at SQL Saturday next weekend - be sure to say hi!

*If you would like to join us for lunch, we ask that you pay $10 to help us cover the cost of feeding a few hundred people. However, if you'd rather bring your own, that's ok too - just go through the registration process like normal. When it asks you to pay for lunch at PayPal, go ahead and close that window.

Resolving Errors from Deployment of Database to Azure SQL DB

This week our team at BlueGranite decided that the sample relational database called ContosoRetailDW would be best located for our demo & training purposes in Azure as a SQL Database. Below are the steps taken to get this sample database in a state where it could be deployed as an Azure SQL DB. I will list my process from beginning to end, although I won't bother detailing out all of the early steps.

Download the Sample Database

First, I downloaded the Contoso database backup file, extracted it, and restored the backup to a SQL Server 2014 instance. Then I verified it can be queried in SQL Server Management Studio.

Azure Management Portal

In the Azure management portal, I created a small Azure SQL DB just for testing purposes. Since this was the first Azure SQL DB created in this Azure subscription, I wanted to do a small test to obtain a server name, set the server firewall, and check connectivity.

You can find the server name on the Servers page. To set the server firewall, click the Manage button, and respond Yes to the first prompt.


Connect to the Azure server from SSMS for a quick verification of connectivity to the test database created as an Azure SQL DB. 

Good. Now that I know I can connect ok to Azure, I'm ready to move onto the task at hand with migrating the Contoso sample database.

First Attempt to Deploy to Azure SQL DB from SSMS

From within SSMS, connect to the instance where the database to be migrated exists. Right-click the database > Tasks > Deploy Database to Windows Azure SQL Database. 

Well, if the story ended here it wouldn't have any drama, right?  With my first attempt I received a list of errors due to unsupported features. I initially tried to rectify them in SSMS but it's just not the best tool for the job so I'll skip that party of the story.  Enter Visual Studio.

Removing Unsupported Features in Visual Studio

Open Visual Studio (SQL Server Data Tools). Connect to your SQL Server instance in SQL Server Object Explorer. We will need a project in order to alter the schema to remove unsupported features.  So, right-click the Contoso database and choose Create New Project.

I named mine as shown below since it's purpose was to convert the original database schema to where it's supported in Azure SQL DB.

Now that we have a project that is based on the Contoso Retail DW database, the first thing to do is change the target platform to Microsoft Azure SQL Database. This is done in the project properties.

Now that the project understands its destination will be an Azure SQL DB, let's do a build and see what happens.

Same list of errors that I got previously in SSMS. However, now we're in a lot better position to address them quickly.

There's an MSDN blog entry which discusses a quicker way to address the issues. I went ahead and handled them individually.  It didn't take very long at all.  

Double-click on each error in the Error List and it'll take you to the DDL where the unsupported syntax exists. 

One of our two main issues is data compression.  That required the "WITH (DATA_COMPRESSION = PAGE) syntax to be removed from some of the tables.

Another one of the issues was extended properties which aren't supported in Azure SQL DB at this time. So, even though they are nice for documenting a database, I went ahead and removed the extended properties from each object.

Let's try another build (a rebuild actually, to make sure everything gets evaluated). And now that all of the errors are rectified we are happy and ready to move on.

Schema Comparison in Visual Studio

Now it's time to get the real database sync'd up with the changes made to the project schema. So let's create a schema comparison under Tools > SQL Server > New Schema Comparison.

For the left side I used the project (i.e., the "fixed" one) and for the right side I used the database (i.e., the "wrong" one that needs to be "fixed"). 

Looking down the list, I see some deletes and some changes. They all look reasonable. Click on the Script button on the SqlSchemaCompare toolbar.

Now I have a script that I can run against the database to get its DDL updated.  Scrolling down through the script, everything looks reasonable.  Next step is to Connect to the database server using the icon on the toolbar.

Once connected, we can execute the script.

Mine took 18 minutes to run on a laptop.

Deploy from SSMS to Azure SQL Database

Now that the unsupported features have been addressed, let's try the deployment again. In SSMS, right-click the database > Tasks > Deploy Database to Windows Azure SQL Database.

We are presented with a wizard.

Connect to the server in Azure.  The server name from Azure for a SQL DB is appended with "".  Be sure to use SQL Authentication. 

I changed the maximum database size from the default of 1 GB to 5 GB, since my version of the Contoso Retail DW is about 3.9 GB. 

You're presented with a summary page before choosing Finish. Double check your Source Database is less than the Maximum Database Size selected for the target with some room to spare. If the source is larger, it'll still deploy what it can until it runs out of space.

As it's working you'll see status messages.

Mine took a couple of hours to deploy the ~4GB database. Now our sample Azure SQL DB is ready to be used.

Verify Connectivity to Azure SQL DB

In SSMS, connect to the instance & do a quick test to verify connectivity is working.

Using Power Query to Get Data From an Excel File in OneDrive Via URL

In this blog entry I'm going to discuss accessing an Excel file that's stored in OneDrive. What I do *not* want to do is associate the files to my local file path such as c:\users\melissa\onedrive... because another person who downloads the workbook and wants to edit or look at the Power Query settings will get an error. So I want to use the URL for OneDrive to make it easier to pass this workbook among other people. This took a bit of trial and error to find the right URL that would work with Power Query.

Storing the Source File in OneDrive

The first thing we want to do is get a file in OneDrive and make sure it's shared. For this purpose I'm just using a little simple file called PowerQueryTest, directly in my Public OneDrive folder. However, this same logic can be applied to a file shared with a set of coworkers.

Getting a Link for the Source File

If you click the Excel file to view or edit it in the browser, the URL looks like this:

Power Query doesn't like that URL. So, the next thing you might think to try is to generating a link with the Share feature. For my file, that URL looks like:

The 2nd URL is a little different from the first one, but the resid that identifies the report is the same so that's good. But, the above format still won't work in Power Query.

What I've found that will work with Power Query is this kind of URL structure:  


Basically, it's followed by the unique report # which you can obtain from the URL as you view or edit a report in the browser.

Using the URL in Power Query

Even though we're going after contents of an Excel File, we want to use the From Web  menu item on the Power Query ribbon (instead of From File). Paste in the URL in the correct format mentioned just above.

In the Navigator menu on the right pane, select the sheet and then Edit.


Now you can carry on with whatever steps you need for your file. For my teensy little set of sample data here, I told it to "Use First Row as Headers" and then gave it a name and description in the query properties.

Credentials to Access to One Drive from Power Query

Now, the next thing you need to know is what will happen when the next person opens this workbook and wants to edit or even just look at how the Power Query steps. So, for this last series of screen shots, we are seeing it from the perspective of person #2 who just opened the file.

The first thing person #2 sees is the normal security warning. It's ok to click Enable Content since it's been shared by our trusty coworker. 

When we hover over the query name in the Workbook Queries pane, we see a warning in the popup box.  Permission Error: Credentials are required to connect to the Web source.  That makes sense, even though this was a file I had in the public share - Power Query just doesn't know it yet.  

Go ahead and click Edit.

Then choose Edit Credentials.

Lastly, select Anonymous access.  For me, the first radio button worked perfectly.

You might want to let your coworkers know to go ahead and choose anonymous credentials if they see the warning and want to edit, view, or refresh Power Query. (Of course, if it's not anonymous then let them know which organizational account to sign in with instead.)

Power Query Saved Data Source Settings

On the Power Query ribbon, there's a Data Source Settings item in the Machine Settings section. After person #2 set their credentials in the previous step, their machine now stores this setting for OneDrive.

That's it! Found a different way? Leave a note in the comments.

As always, Power BI is constantly evolving and changing. This information is correct as of Aug. 2014.

You Might Also Like...

Power Query Data - Should It Be Loaded to the Worksheet?