F1-SQL Server

2017-09-06

SSMS 17.x failed to connect to SSIS

Problem

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: "The specified service does not exist as an installed service.".”

If you have more services installed as SQL Server engine, SSAS or SSRS and confirming that all of those services are up and running:

You will be able to see them in SSMS v17.x when browsing for services but realize that SSIS services it is not being listed:

Solution

Actually the solution is very simple but is not so obvious. After some tries to understand and solve this issue, a downgrade of the SSMS version solved it. To connect to a SSIS 2016 instance you will need at least the SSMS v16.5.3.

For some reason, SSMS v17.x (at the moment I am writing this article the last version is 17.2) has a limitation that does not let it to be able to connect to any SSIS version prior that 2017. So if you need to use SSMS to work with SSIS the best you can do by now is to download and install SSMS v16.5.3.

Note:

As stated above, this issue happens with SSMS v17.2 and previous minor releases of SSMS v17. It has been already reported to Microsoft so it is expected that in a future release this will be corrected. Meanwhile use an older version of SSMS when working with SSIS instances.

You can track the SSMS releases bug fixes here to see when this specific bug will be fixed by Microsoft.

2015-11-09

Database migration - The Copy Database Wizard method

The Copy Database Wizard method like the name says is a wizard that can be used for easily moving databases from one instance to another. During the wizard you will be asked to provide the source and destination servers and you will be able to select one or more databases to migrate at once, specifying the target location. Additional objects as Logins, SSIS packages, SQL Agent Jobs, Endpoints, User-defined error messages and Stored Procedures from master database can also be included and transferred during the process.

The Copy Database Wizard (CDW) isn't available in SQL Server Express Edition since this tool requires SQL Server Agent. Another limitation is that CDW can't be used to migrate system databases and databases marked for Replication, Inaccessible, Loading, Offline, Recovering, Suspect or in Emergency Mode.

The SQL Server Agent must be started and running in the target server and the user that runs the CDW jobs must have sysadmin server role in source and target servers.

The Copy Database Wizard is available in SQL Server Management Studio by expanding Databases Folder and right-clicking on a database name then chose Tasks / Copy Database... option in the context menu:

The wizard will start by requesting the credentials for source and target servers and then will ask for the Transfer Method:

The Use the detach and attach method will basically do what I already described in my previous article SQL Server database migration - The Detach/Attach method so just follow the wizard for this option and read my article to be aware of the details before starting to Copy the database(s). Just keep the check on "If a failure occurs, reattach the source database" option so you won't lose the original database in case of any failure.
The Use the SQL Management Object method will use SMO (SQL Management Object) to transfer the objects and data from the source server to the target server so it will be more slower but the source database will be online during all transfer process.
The next step is to select the database(s) to be transferred. The transfer can copy or move operation and when you select move it means that the source database will be dropped after transfer done. The Status informs you if the database can be transferred (OK) and will display the reason why the database cannot be transferred (System database or the database status).



After choosing the database(s) to be transferred the next step is to configure the database(s) in the target server. In the Configuration screen you can change the destination database name, path and filename. You can also chose what to do if a database or filename already exists in the target server. This screen will appear for each database selected previously.


If the Source and Target servers are different then the next step give us the opportunity to chose server objects that we want to be transferred with the database. Logins used by the source database are selected by default but can be removed if wanted. Others possible server objects are presented in the following screen:


NOTE: Extended stored procedures and their associated DLLs are not eligible for automated copy.

If you selected the Use the detach and attach method then the next step is to specify for each database the correspondent file share containing the source database files:

NOTE: The user that will run this transfer package will need Read & Write permissions in the target folder.

CDW creates a SSIS package to transfer the database and the next step is where you can customize the package as your preference by accepting or changing the default suggested name and choosing to log the operation on not. If yes, you can store the log in Windows Event Log system or into a Text File that will be created by default in the same location as the target database files will be but you change the location.


Last step is to chose between run the job immediately or schedule it for later run. Do not forget that the user choose for Integration Services proxy account should have sysadmin role in the target server or the job will fail with an Access Denied error.


To complete the Wizard review all the choices made and if all ok click on Finish and the job should run successfully and the selected database(s) transferred from the source instance to the target one.

Post migration considerations:
- If you use the detach and attach method to migrate from an older SQL Server database version you will need to change the compatibility level of the new migrated database to the target SQL Server version. This step is not necessary if you use the SQL Management Object method to migrate the database since it will create the new database with the current SQL Server version.
- Rebuild all indexes is recommended after transferring a database. It's also recommended to perform a full database backup.
- If you use the SQL Server Management Object method to transfer a database with full-text catalog, then you will must repopulate the index after the transfer.

2015-11-04

Database migration - The Backup & Restore method

The Backup & Restore method can be used to reduce the downtime period during the database migration process by performing a full backup and restore it in the new MSSQL instance while the users are still logged in and working so the downtime can be shorter by applying a differential or transaction log backup (depending on the database recovery model) after all users being disconnected and the database does not have any activity, meaning that downtime is limited to the time of the differential/transaction log backup, copy and restore tasks.

NOTES:

This migration method is for user databases only. System databases, Jobs, DTSX Packages, Reports and any other database object are not covered in this article.
A sysadmin role is necessary to execute any of the provided steps.

Before start - Only for Upgrades

In the case of a migration being part of an upgrade then the first pre requisite is to run the SQL Server Upgrade Advisor to analyze the earlier version of the SQL Server database, and then generates a report that identifies issues to fix either before or after the upgrade. Those issues are not covered by this article either.

Step 0 - Preparation

The database files on the destination server will be the same size as the database files on the source server, so for larger files you can shrink them before performing the database full backup so smaller files will be created during the restore on the destination server. For data files use shrink with truncate only option so will only release all free space at the end of the file to the operating system but does not perform any page movement inside the file. It will be faster and will not cause fragmentations.

Step 1

Run a full backup on the source database by using the interface provided by SSMS (right-click on the database name, chose Tasks/Backup...:

The same operation can be executed by the following T-SQL commands:

BACKUP DATABASE [EE] TO  DISK = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\EE.bak' WITH NOFORMAT, INIT,  NAME = N'EE-Full Database Backup'

Note: "EE" is the database name used for this example. You need to replace it with your real database name.

Step 2

Copy the the full backup file from the source location backup path to the destination server.

Important: In the case a rollback will be necessary this backup file can be used to perform that task.

Step 3

Connect to the destination server and restore the full database backup made in step 1. Specify the WITH NORECOVERY option if you will apply differential and/or transactional log restores then only the restore of differential or transaction log backup in the destination server will be running the WITH RECOVERY option.

You can restore the database by using the interface provided by SSMS (right-click on the Databases folder and chose Restore Database...) and locate the backup files by clicking the "From device" button. Check the Restore cell and provide a name for the new database. In case of providing an existing name then the database will be overwrited:

Click on Options to specify the WITH NORECOVERY state if you will apply a differential or transaction log restore after, or if you want to change the data and transaction log files location when the path is different from the source server. In case of restoring into an existing database check the WITH REPLACE restore option:

The same operation can be done with the following T-SQL command:

RESTORE DATABASE [EE] 
FROM  DISK = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\EE.bak' 
WITH  FILE = 1,  
 MOVE N'EE' TO N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Data\EE.mdf',  
 MOVE N'EE_log' TO N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Data\EE_1.LDF',  
 NORECOVERY,  NOUNLOAD
GO

If the full backup was performed during a period that there was no activity on the database so you ran this step with the Recovery option then you can skip the next step. Otherwise if the NoRecovery option was applied your new database should be in Restoring status:

Step 4 - Only for minimal downtime

Now that the tasks that took more time were done and all the users has been disconnected and no more activities are running on the database is time to perform a transaction log backup or a differential database backup (depending if the database has respectively Full or Simple recovery model set) so the additional database modifications will be applied and stored in the migrated database.

Run a differential or transactional backup on the source database by using the interface provided by SSMS (right-click on the database name, chose Tasks/Backup... The Backup Type combo box has the available options for the backup types. No more options need to be changed since the Recovery option is the default and it will keep the file locations from the previous restore:

The same operation can be executed by the following T-SQL commands:

BACKUP LOG [EE] TO  DISK = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\EE.trn' WITH NOFORMAT, NOINIT,  NAME = N'EE-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD

Copy the the new backup file from the source location backup path to the destination server.

Important: In the case a rollback will be necessary this backup file and the previous full backup file can be used to perform that task.

Connect to the destination server and restore this backup by applying the WITH RECOVERY option.

You can restore the database by using the interface provided by SSMS (right-click on the new databases folder and chose Restore...) and locate this backup file by clicking the "From device" button:

The same operation can be done with the following T-SQL command:

RESTORE LOG [EE] FROM  DISK = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\EE.trn' WITH  FILE = 1,  NOUNLOAD

The database should be now online and available.

Step 5 - Only for Upgrades

In the case of a migration being part of an upgrade then there is an extra step that is to change the compatibility level of the migrated database so his behaviour will be compatible with the current version of SQL Server.

After that you should rebuild all indexes but if time is an issue then alternatively run DBBC UPDATEUSAGE on the database to correct any incorrect row or page counts and update the statistics for all the tables in the database with the UPDATE STATISTICS command.

Step 6

After database migration you will need to transfer the logins and passwords from the old instance to the new one and map those logins to the database users. Microsoft has a good and complete article about that so I just leave here the link to the article.

After all these steps are done it is highly recommend that you perform a full backup of the migrated database.

2015-10-30

Database migration - The Detach/Attach method

Migrating a database is moving it to another instance, within the same server or to another server. Many of the database migrations also includes an upgrade, meaning that the database will be moved to a more recent version of the database engine.In this article I will describe one possible migration method and will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

The Detach/Attach method can be used if the downtime period is not an issue since the database will be unavailable during all process.
NOTES:

This migration method is for user databases only. System databases, Jobs, DTSX Packages, Reports and any other database object are not covered in this article.
A sysadmin role is necessary to execute any of the provided steps.

Some considerations
A database can only be detached if is not currently in use so assure that no one is connected before detaching the database. Update statistics can be performed to update information about the data in the tables and indexes. Also requires exclusive access to the database.

If the database is being replicated, it must be unpublished first before be detached.
If a database snapshot exists on the database, before you can detach the database, you must drop all of its snapshots.
If the database is being mirrored then it cannot be detached until the database mirroring session is terminated.

Before start - Only for Upgrades
In the case of a migration being part of an upgrade then the first pre requisite is to run the SQL Server Upgrade Advisor to analyze the earlier version of the SQL Server database, and then generates a report that identifies issues to fix either before or after the upgrade. Those issues are not covered by this article either.

Step 0 - Preparation
Make sure that you know the name and the current location of all data files and log files for the database since you will need to know which files will be copied.
For larger files you can shrink them before detach the database so will save time during the copy process. For data files use shrink with truncate only option so will only release all free space at the end of the file to the operating system but does not perform any page movement inside the file. It will be faster and will not cause fragmentations.

Step 1
For detach the source database you can use the detach interface provided by SSMS (right-click on the database name, chose Tasks/Detach... and then check the Drop Connections and Update Statistics options.

The same operation can be executed by the following T-SQL commands:

USE [master]
GO
--Disconnect any connection to the database to assure nobody else is connected
-- and set it to Single User mode to require exclusive access on the database
ALTER DATABASE [EE] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
-- Perform Update Statistics and Detach the database
EXEC master.dbo.sp_detach_db @dbname = N'EE', @skipchecks = 'false'
GO

Note: "EE" is the database name used for this example. You need to replace it with your real database name.

Step 2
Now you can copy the database files (usually .mdf, .ndf, and .ldf files) that are associated with the database, from the source location path to the correct path in the destination server.
Important: Do not delete the source files after the copy since they will be used to attach back the database if a rollback will be necessary.

Step 3
Attach the database on the destination server by pointing to the files that you just copied to the destination server in the previous step.
You can attach the database by using the interface provided by SSMS (right-click on the Databases folder and chose Attach...) and locate the .MDF file by clicking the "Add..." button.

All the information presented in this screen is the information from the source database, like the Database Name, Owner and the file names and locations.
If there is already a database with the same name then you will need to provide a new name by clicking in "Attach As" column, and for change the file locations click in the "..." button in front of the desired file in the "Current File Path" column and locate the correct file or files (they should be in the path where you copied them on the previous step).

The same operation can be done with the following T-SQL command:

USE [master]
GO
CREATE DATABASE [EE] ON 
( FILENAME = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Data\EE.mdf' ),
( FILENAME = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Data\EE.ldf' ),
( FILENAME = N'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Data\EE.ndf' )
 FOR ATTACH
GO

Step 4 - Only for Upgrades

Step 5
After database migration you will need to transfer the logins and passwords from the old instance to the new one and map those logins to the database users. Microsoft has a good and complete article about that so I just leave here the link to the article.
After all these done is highly recommend that you perform a full backup of the migrated database.

2015-06-03

Understanding the Views (iii) - The complete security layer

In my first article I showed how to use a view to limit the access to data by returning only the rows that each user should see. Of course it is also possible to filter by columns and should be the right thing to do since a simple SELECT * would may return more data than the necessary and may also cause more network traffic.
Imagine that in a company's Intranet there's a page with all Employees and the data is provided by the Human Resources department. For sure no one wants that their more private information to be available for the rest of the company then fields like address, birthday, civil status and children's won't be published. A good solution is to create the View with the limited data and give access to all employees so they can see the information in the webpage:

CREATE VIEW EmployeesData
AS
(SELECT E.EmployeeID, D.DepartmentName, E.EmployeeLastName, E.EmployeeFirstName, C.CategoryName
FROM EmployeeSalary E
 INNER JOIN Department D ON (E.DepartmentID = D.DepartmentID)
 INNER JOIN Category C ON (E.CategoryID = C.CategoryID))
GO

GRANT SELECT ON EmployeesData TO [MyDomain\Employees]
GO

Do not forget that users doesn't need to have permissions on any of the above tables because of the ownership chaining I referred in my previous article.
You might realize that I added more tables and INNER JOIN clauses. That is to show that we can have complex Views and it is a good way to encapsulate and reuse the code. For example if someone from IT department wants to know how many people are working in their department or who are their department colleagues, they can use the View:

-- How many people are working in IT department
SELECT COUNT(*)
FROM EmployeesData
WHERE DepartmentName = 'IT'

-- Who are the people working in IT department
SELECT *
FROM EmployeesData
WHERE DepartmentName = 'IT'

Continuing with our company's Intranet we want to provide a web page with employee's salary receipt so each employee can only see their own information. With the View created in my first article we can easily join with this one created here and we will have all the necessary information:

CREATE VIEW EmployeeSalary AS
(SELECT *
FROM EmployessData E
INNER JOIN AllSalaries S ON (E.EmployeeID = S.EmployeeID))

Don't forget that AllSalaries's View it's already limiting the data by the EmployeeID so each employee will only see his relevant information.

Also be careful when using nested Views. You can lose control of them with more complex queries and will be harder to debug apart of a possible negative impact in the performance. This is only a simple example to show that it's possible to create nested Views. Be wise when using them.

2015-05-29

Understanding the Views (ii) - Ownership chaining

This is my second article about the Views and is a continuation from my previous one. In this one I will only focus on the security part of the Views. In my previous article I explained how you can secure data with Views limiting the scope of the returned information depending on the person who wants to access the data. In this article I will talk about the ownership chaining.

As we saw in my previous article when we create a View for querying our tables we do not need to give permissions on those tables but the View only. And why is that? It's because SQL Server knows that we are the owner of those tables and so it uses the ownership chaining to bypass the necessary permissions checks. This behavior it's how the engine works and can't be changed.

It's possible to grant permission on a database object and allow the granted user to grant his permission on the object to others. In my experience this very rarely has to happen but since it is possible to do I decided to write this article to call some attention to when and if you need to do that.

Imagine that the Chief Financial Officer (CFO) of a company needs to have access to salary information of the company. We won't give him access to the main table but will create a View that selects all data from the salary's table:

CREATE VIEW AllSalaries AS
SELECT *
FROM Salary

Then grant him the select permission on the View. But during his absence he needs to hand over his work to his deputy so we'll need to give him the option to grant himself the necessary permission to give access to his deputy, so the WITH GRANT OPTION will do the trick:

GRANT SELECT ON AllSalaries TO [MyDomain\CFO] WITH GRANT OPTION

Can you see a security hole here?

If we are talking about a View that works with sensitive data the information can be spread to unwanted people. Of course we could give immediately the SELECT permission to the CFO and his deputy but imagine that the CFO wants to control when his deputy can access the data, or even if he has more than one deputy he wants to choose to whom he's going to give permission depending on his abscence period.

So, how to avoid this without saying NO to our CFO and ensure that his deputy won't give permissions on the View to more people?

DENY command is the answer since it precedes the GRANT permission. Of course if there are a lot of users in the company we would need to write a DENY command for each user. To avoid that work we can ask for creation of an AD group where these users will be added except the CFO and his deputy or deputies. Then the DENY command will be something like this:

DENY SELECT ON AllSalaries TO [MyDomain\DeniedUsers]

Then even someone gives access to one of the users in the Denied group the AllSalaries view won't return an error but will raise the following error if that user try to access the View:

The SELECT permission was denied on the object 'AllSalaries', database 'EE_DB', schema 'dbo'.

2015-05-25

Understanding the Views (i) - Limiting the access to data

It can be said that a View is a virtual table defined by a query and that it also has columns and rows. There are several occasions to use a View, but in this article I will focus only in the security mechanism that is one of the principal reasons for using a View.

One of the purposes for creating a View is to allow users to access data without granting them permissions to the base tables. Imagine your company's Human Resource database. Every employee has access to his data, but how can you ensure that each employee can only see information related to him?

For example, if you give read permission to the Salary table:

GRANT SELECT ON Salary TO [MyDomain\Employees]

MyDomain\Employees represents an AD group with all Employees users

Everyone now can see his own information by running a simple SELECT command:

SELECT *
FROM Salary
WHERE EmployeeID = USER_NAME()

Let's keep things simple so for this company the EmployeeID is the user's LogonID, so we can match the AD with the table primary key.

But they can also see any colleague's information by replacing the USER_NAME() with the corresponding colleague's ID... or even worse, they can see the salaries of all employees with a simple SELECT * FROM Salary command.

To avoid these situations we can create a View and give permissions only to that View instead of the Table. Below is the View definition:

CREATE VIEW EmployeeSalary AS
SELECT *
FROM Salary
WHERE EmployeeID = USER_NAME()

Now let's revoke the permissions that we gave to the Salary Table and give permission only for the View:

REVOKE SELECT ON Salary TO [MyDomain\Employees]
GO

GRANT SELECT ON EmployeeSalary TO [MyDomain\Employees]
GO

The user will have only access to the View and can't even see the Tables or other Views that may exists in the database.

That can be confirmed if the user tries to run a SELECT statement over the Salary Table. Will raise the following error:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'Salary', database 'EE_DB', schema 'dbo'.

Each employee now can only have access to the information that respects to him and none else.