Archive for the ‘SQL Server 2014’ Category

I can’t find BIDS(SSDT) tool after SQL Server 2014 installation on my laptop. I thought I might have missed it so I ran the media again to add features but all features were already installed. I am very confused :(. Why has this tool been excluded from the media?

Where is BIDS or SSDT on SQL Server 2014

So I Googled it and found this blog below written by Michael Otey.


Microsoft apparently has replaced BIDS (2008R2) and SSDT (2012) with SSDT-BI in 2014!!! They can be downloaded from http://msdn.microsoft.com/en-us/data/hh297027


Quoted from Microsoft:

SSDT-BI, SQL Server Business Intelligence is a distinct toolset from SSDT or the SQL Server database tooling in Visual Studio 2013. As part of the SQL Server 2014 release, SSDT-BI has released a version for Visual Studio 2013. 

The SSDT-BI for Visual Studio 2012 will be installed under the Microsoft SQL Server 2014 CTP2.


Read Full Post »

I honestly don’t remember that I need to run SQL Server Management Studio (SSMS) as Administrator to connect to the Integration Server 2008R2. However, this has changed in both SSIS 2012 and SSIS 2014.

I have a laptop running on Windows 7 and SSIS 2014. I need to run SSMS as Administrator to connect to the Integration Server 2014. Why? Below is the error that I get when trying to connect as me, even though I am part of the Administrators local group.

SSIS 2012 and 2014 access error via SSMS

To resolve this issue on Window 7:

1. You must be part of the Distributed COM Users group.

Go to Control Panel > User Accounts > Manage User Accounts  and then select the Advanced tab. Under the Advanced User Management, click on the Advanced button. Under the Local Users and Groups, select Groups and then select Distributed COM Users group. Add your username or Users group into this group.

Add user into Distributed COM Users group

2. Allow remote connection to the Integration Service on DCOM Config.

Go to Control Panel > Administration Tools > Component Services. Navigate to Computer folder > My Computer >DCOM Config. Locate either MsDtsServer100 (for SSIS 2008R2) or Microsoft SQL Integration Services 11.0 (for SSIS 2012) or Microsoft SQL Integration Services 12.0 (for SSIS 2014).

Right click on the item and then select Properties. Select the Location tab and tick the “Run application on this computer” option.

DCOM Config - enable running the application on this computer

3. Select the Security tab and then under the Launch and Activation Permissions, select the Customize option and then select the Edit button.

DCOM Config - allow remote access

Add your username or user group and then tick local launch and local activation. Tick remote launch and remote activation if the server is located on a different server.

DCOM Config - Allow local launch and local activation

3. Select the Security tab and then under the Access Permissions, select the Customize option and then select the Edit button. Add your username or user group and then tick local access and remote access.

DCOM Config - Allow local access and remote access

4. Restart the SSIS service (this is very important as changes will not be applied without a restart).

5. Test connecting to the SSIS via SSMS.

6. Note: Connecting to SSRS via SSMS will work too once the above configuration is applied.

Read Full Post »

Once I have SQL Server 2014 installed successfully on my laptop, I went directly to configure the Data Quality Service (DQS) i.e. create catalog i.e. SSISDB database. I then went to configure the Master Data Service (MDS) without making sure that I have all the pre-requisites installed, which was the IIS components.

So I ran the MDS Configuration Manager, I had a green tick on the Windows Power Shell but a yellow exclamation on the IIS. I then navigated to the Database Configuration to create a new database called MDS. Next, I navigated to the Web Configuration, it said I have not got IIS installed. So I went to “Programs and Features” > “Turn Windows Features on and off” to install the required components for IIS. I ticked all components under IIS except for IIS 6 Scripting Tools and IIS 6 WMI Compatibility and FTP server. This then requires a reboot. Once rebooted, I ran the MDS Configuration Manager and I still had a yellow exclamation on the IIS under pre-requisites. This was odd. I thought I need to create a website first. So I created a Default Website and called it MDS. The website was created successfully but it threw an error complaining about ASP.NET not installed. ASP.NET was indeed installed. This time I resolved to Google. One blog listed out that I needed to add extra features below: Add the following features: – .NET Framework 3.0 Features – WCF Activation HTTP Activation Non-HTTP Activation I then navigated to check if those features above were installed. They weren’t. So installed them and restarted the IIS service (just in case). This time it threw a different error below: Handler “PageHandlerFactory-Integrated” has a bad module “ManagedPipelineHandler” in its module list I had no idea what the error means so I Googled again. One blog suggested that I needed to re-register my asp.net. C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -I This indeed has resolved my issues. My MDS has now been configured properly 🙂 MDS

Read Full Post »

%d bloggers like this: