Tag: SQL server

Login is from an untrusted domain and cannot be used with integrated authentication

if you are using this connection string in SSIS using windows authentication

Data Source=myserver;Initial Catalog=mydb; Integrated Security=SSPI;Connect Timeout=15;

You will be able to develop your package and run. When it’s get deployed to SQL Server and someone try to run the package, they will get this error.

The reason, windows computer is not joined with Active Directory.

If you still want to run your SSIS package on server, use SQL Server authentication and use this connection string;

Data Source=myserver;Initial Catalog=mydb;User Id=myuser;password=mypassword;Connect Timeout=15;

Hope this will help

How to verify SQL Server is using NTLM / Kerberos authentication

You can execute the below TSQL Query to verify authentication used by SQL Server Connections.

USE master
GO

SELECT auth_scheme FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;
GO

Expected Results

SQL – When SQL Server authentication is used
NTLM – When NTLM authentication is used
KERBEROS – When KERBEROS authentication is used

Reference

How to Verify and Register SPN for SQL Server Authentication with Kerberos Connections

Run an SSIS package from SSMS with Transact-SQL

This quickstart demonstrates how to use SQL Server Management Studio (SSMS) to connect to the SSIS Catalog database, and then use Transact-SQL statements to run an SSIS package stored in the SSIS Catalog.

SQL Server Management Studio is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database. For more info about SSMS, see SQL Server Management Studio (SSMS).

Prerequisites

Before you start, make sure you have the latest version of SQL Server Management Studio (SSMS). To download SSMS, see Download SQL Server Management Studio (SSMS).

An Azure SQL Database server listens on port 1433. If you’re trying to connect to an Azure SQL Database server from within a corporate firewall, this port must be open in the corporate firewall for you to connect successfully.

Supported platforms

You can use the information in this quickstart to run an SSIS package on the following platforms:

You cannot use the information in this quickstart to run an SSIS package on Linux. For more info about running packages on Linux, see Extract, transform, and load data on Linux with SSIS.

For Azure SQL Database, get the connection info

To run the package on Azure SQL Database, get the connection information you need to connect to the SSIS Catalog database (SSISDB). You need the fully qualified server name and login information in the procedures that follow.

  1. Log in to the Azure portal.
  2. Select SQL Databases from the left-hand menu, and then select the SSISDB database on the SQL databases page.
  3. On the Overview page for your database, review the fully qualified server name. To see the Click to copy option, hover over the server name.
  4. If you forget your Azure SQL Database server login information, navigate to the SQL Database server page to view the server admin name. You can reset the password if necessary.

Connect to the SSISDB database

Use SQL Server Management Studio to establish a connection to the SSIS Catalog on your Azure SQL Database server.

  1. Open SQL Server Management Studio.
  2. In the Connect to Server dialog box, enter the following information:TABLE 1SettingSuggested valueMore infoServer typeDatabase engineThis value is required.Server nameThe fully qualified server nameIf you’re connecting to an Azure SQL Database server, the name is in this format: <server_name>.database.windows.net.AuthenticationSQL Server AuthenticationWith SQL Server authentication, you can connect to SQL Server or to Azure SQL Database. If you’re connecting to an Azure SQL Database server, you can’t use Windows authentication.LoginThe server admin accountThis account is the account that you specified when you created the server.PasswordThe password for your server admin accountThis password is the password that you specified when you created the server.
  3. Click Connect. The Object Explorer window opens in SSMS.
  4. In Object Explorer, expand Integration Services Catalogs and then expand SSISDB to view the objects in the SSIS Catalog database.

Run a package

Run the following Transact-SQL code to run an SSIS package.

  1. In SSMS, open a new query window and paste the following code. (This code is the code generated by the Script option in the Execute Package dialog box in SSMS.)
  2. Update the parameter values in the catalog.create_execution stored procedure for your system.
  3. Make sure that SSISDB is the current database.
  4. Run the script.
  5. In Object Explorer, refresh the contents of SSISDB if necessary and check for the project that you deployed.

SQLCopy

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name=N'Deployed Projects',
	  @project_name=N'Integration Services Project1',
  	@use32bitruntime=False,
	  @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
    @object_type=50,
	  @parameter_name=N'LOGGING_LEVEL',
	  @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Reference

https://docs.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-tsql-ssms?view=sql-server-ver15

How to split string in SQL Server

There could be many method but here are two simple one;

DECLARE @ValueToSplit NVARCHAR(50) = N'12345VA [987]'

--This is classical method to split string
SELECT
	SUBSTRING(@ValueToSplit, 0, CHARINDEX('[', @ValueToSplit)) AS [FIRST],
	SUBSTRING(@ValueToSplit, CHARINDEX('[', @ValueToSplit)+1, LEN(@ValueToSplit)) AS [SECOND]

--This function can be used in SQL 2016 onward
SELECT split.*
FROM
(
	SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RowNumber, value AS TextValue FROM STRING_SPLIT(@ValueToSplit,'[')
) split
WHERE 1=1
AND split.RowNumber = 1

Here are the results;