Product:

Microsoft SQL server

Issue:

What user is connected with what SQL login?

Solution:

Run this query’s:

SELECT A.name as userName, B.name as login 
FROM sys.sysusers A 
FULL OUTER JOIN sys.sql_logins B 
ON A.sid = B.sid

 

https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysusers-transact-sql?view=sql-server-ver16

select sp.name as login,
sp.type_desc as login_type,
sl.password_hash,
sp.create_date,
sp.modify_date,
case when sp.is_disabled = 1 then 'Disabled'
else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R')
order by sp.name;

https://dataedo.com/kb/query/sql-server/list-logins-on-server

SELECT *
FROM master.sys.sql_logins;

More SQL information:

https://www.w3schools.com/sql/sql_quickref.asp 

Product:

Microsoft Azure Data Factory

Issue:

How do i start a pipeline to run now and not wait for the scheduled run?

Solution:

Go to https://portal.azure.com/ and select you Azure Data Factory resource.

Click on button “Launch Studio”.

Click on pencil icon (author) on the left.

Expand Pipeline, so you see a list of your pipeline.

Double click on the one you want to start.

Then on top you click on Trigger to run that job.

Select “Trigger Now”

Click OK on Pipeline Run dialog.

Click on the monitor icon – and see if it is started under pipeline runs.

 

 

More Information:

https://learn.microsoft.com/en-us/azure/data-factory/concepts-pipeline-execution-triggers 

https://www.mssqltips.com/sqlservertutorial/9398/building-an-azure-data-factory-pipeline-manually/

Product:

Microsoft Azure SQL

Issue:

Error when try to create a user in AZURE SQL from a Managed Identity Object ID.

Principal ‘xyz’ could not be found or this principal type is not supported.

Cannot add the principal ‘xyz’, because it does not exist or you do not have permission.

Solution:

Use the managed resource name instead of the object id.  Managed Identity can be replaced with the resource name, when referring to the object.

Background:

To make Azure Data Factory to connect to a Azure SQL resource with Managed Identity, you need to create a Managed identity for the ADF resource.

Then you can add the resource to AZURE SQL with below command to give it access to the database.

Login to the AZURE SQL server with SSMS, use the Azure SQL server name to connect.
Select the database and click New Query:

CREATE USER [adf_name] FROM EXTERNAL PROVIDER

ALTER ROLE [db_owner] ADD MEMBER [adf_name]

By adding a USER direct to the database, and not create a login in SQL, the user must provided the database name when it connects to AZURE SQL.

 

If you are Owner of the Azure database, then you do not need to be AAD Admin to be able to perform the change in SSMS.

“Azure Active Directory authentication allows you to centrally manage identity and access to your Azure SQL Database.”

 

More Information:

Connect Azure SQL from Data Factory using Managed Identity

https://learn.microsoft.com/en-us/azure/private-link/tutorial-private-endpoint-sql-portal

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial?view=azuresql

https://crmchap.co.uk/principal-could-not-be-found-or-this-principal-type-is-not-supported-error-azure-sql-server/

https://www.data4v.com/managed-identity-between-azure-data-factory-and-azure-storage/

Product:

DB2 connect

Issue:

How see what server is in use for the DB2 ODBC connection, when the setup of the DB2 ODBC connection does not work.

Suggested solution:

Go to a command prompt, and go to the folder where the DB2 active client files are installed. Can be C:\Program Files\IBM\SQLLIB\BIN folder.

Run command   db2 list node directory   to see what server you are connecting to.

If it does not work, start the program db2cwadmin to get a CMD prompt where you can run the db2 commands.

 

In windows registry you can see part of the ODBC settings in your setup;

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
“PROD”=”IBM DB2 ODBC DRIVER – DB2COPY1”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PROD]
“Driver”=”C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\IBM DB2 ODBC DRIVER]
“UsageCount”=dword:00000001
“Driver”=”C:\\Program Files\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL”
“Setup”=”C:\\Program Files\\IBM\\SQLLIB\\BIN\\DB2ODBC64.DLL”
“CPTimeout”=”60”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\IBM DB2 ODBC DRIVER – DB2COPY1]
“UsageCount”=dword:00000001
“Driver”=”C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL”
“Setup”=”C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2ODBC64.DLL”
“CPTimeout”=”60”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\IBM DB2 ODBC DRIVER – IBMDBCL1]
“UsageCount”=dword:00000001
“Driver”=”C:\\PROGRA~1\\IBM\\IBMDAT~1\\BIN\\DB2CLIO.DLL”
“Setup”=”C:\\PROGRA~1\\IBM\\IBMDAT~1\\BIN\\DB2ODBC64.DLL”
“CPTimeout”=”60”

More of the DB2 connection is stored in files, that are updated when you run command like this:

Use a bat file to call the config file:

if not exist "\Program Files\IBM\SQLLIB\BIN" goto stopp
cd \Program Files\IBM\SQLLIB\BIN
db2cmd db2setcp " db2 -tvf C:\db2script\catalog.sql " 
:stopp

Catalog.sql file can contain command like this;

UNCATALOG NODE db2prod;
CATALOG TCPIP NODE db2prod
REMOTE db2servername.domain.com
SERVER 60000
REMOTE_INSTANCE db2prod
OSTYPE LINUX;

UNCATALOG DATABASE PROD;
CATALOG DATABASE PROD
AS PROD
AT NODE db2prod
AUTHENTICATION SERVER;

catalog odbc data source PROD;

(PROD is the database name and the ODBC connection name, change to your need)

 

Also check the C:\Windows\System32\drivers\etc\hosts file for any hard coded references to the DB2 server.

Add row like this in the HOSTS file, to point to the DB2 server ip to maybe improve connection start speed;

# the new DB2server values
10.20.30.40   db2servername.domain.com

 

 

More Information:

https://www.dbisoftware.com/db2nightshow/20181214DB2NightZ92.pdf 

https://www.scriptcase.net/docs/en_us/v81/connecting-your-database/ibm-db2/windows/connecting-with-ibm-db2 

https://docs.rackspace.com/blog/ibm-db2-database-administration/

https://en.wikiversity.org/wiki/Hosts_file/Edit 

https://helgeklein.com/blog/where-is-the-hosts-file-on-windows-x64/ 

https://www.inmotionhosting.com/support/website/modifying-your-hosts-file/ 

Product:

Microsoft Azure Network

Issue:

Can I show a diagram over the network in my Azure subscription?

 

Solution:

  1. Log into the Azure portal with an account that has the necessary permissions.
  2. On the top, left corner of the portal, select All services.
  3. In the All services filter box, enter Network Watcher. When Network Watcher appears in the results, select it.
  4. Select Topology.  The network watchers are created in a resource group named NetworkWatcherRG.
  5. Select a subscription, the resource group of a virtual network you want to view the topology for, and then select the virtual network.

 

In Azure you can select the Virtual network, and click on Diagram on the left side.

Then you get a picture of the resource you have in Azure.

 

More Information:

https://learn.microsoft.com/en-us/azure/network-watcher/view-network-topology 

https://learn.microsoft.com/en-us/azure/security/fundamentals/infrastructure-network

Product:

Microsoft SQL server 2016

Issue:

How list only the latest rows with the same date in the update column?

Solution:

Create a view – who will only list the latest row based on the date column;

CREATE VIEW [DM].[AccountView-Latest]
AS
SELECT [key_dimAccount]
,[Name]
,[InsertDate]
,[UpdateDate]
FROM [DM].[dimAccount]
WHERE UpdateDate = ( SELECT MAX(UpdateDate) FROM [DM].[dimAccount ] )

The where statement will only take the latest date (max) from the updatedate column – should give a list with only the records that are updated at same date.

 

More Information:

https://www.w3resource.com/sql/aggregate-functions/max-date.php

https://www.w3schools.com/sql/func_sqlserver_getdate.asp

Product:

Microsoft SQL server 2016

Issue:

How do i see when someone last accessed the database?

Solution:

Use any of this SQL querys;

SELECT d.name,
last_user_seek = MAX(last_user_seek),
last_user_scan = MAX(last_user_scan),
last_user_lookup = MAX(last_user_lookup),
last_user_update = MAX(last_user_update)
FROM sys.dm_db_index_usage_stats AS i
JOIN sys.databases AS d ON i.database_id=d.database_id
GROUP BY d.name

 

Declare @last_boot datetime
Set @last_boot = (select [sqlserver_start_time] from sys.dm_os_sys_info)

select @@servername as [ServerName],'last_boot' = @last_boot, 'days_since_last_boot' = datediff(d, @last_boot, getdate())

if object_id('tempdb..##Table_usage_data') is not null
drop table ##Table_usage_data
create table ##Table_usage_data (ID int identity (1,1), [database] varchar(255),[last_user_seek] datetime,[last_user_scan] datetime, [last_update] datetime)

declare @get_last_user_activity_timestamp varchar(max)
set @get_last_user_activity_timestamp = ''
select @get_last_user_activity_timestamp = @get_last_user_activity_timestamp +
'select db_name([database_id]), max(last_user_seek), max(last_user_scan), max([last_user_update]) from sys.dm_db_index_usage_stats where db_name([database_id]) = ''' + [name] + ''' group by [database_id];' + char(10)
from sys.databases where [database_id] > 4 and [state_desc] = 'online'

insert into ##Table_usage_data ([database],[last_user_seek],[last_user_scan],[last_update])
exec (@get_last_user_activity_timestamp)

select
[database], last_user_scan,last_user_seek,last_update
from
##Table_usage_data

 

More Information:

https://www.dbblogger.com/post/identify-when-your-sql-database-was-last-used

 

Product:

Microsoft Windows

Issue:

Sometimes it is hard to explain a word over the phone – then you can use the phonetic alphabet to help out.

Solution:

 

 

 

 

 

Product:

Microsoft Azure SQL

Issue:

How list size of database?

Solution:

Login to SSMS, go to your database and enter command:

EXEC sp_spaceused;

 

Not all commands are allowed in Azure SQL, as it is a PAAS solution. See here for diffrences:

https://www.codeguru.com/csharp/the-difference-between-sql-server-and-sql-azure/ 

https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison?view=azuresql 

Permissions comparison between on-premises SQL Server and Azure SQL Database

 

The version of Azure SQL is the latest, and is upgraded automatic, you need to manually upgrade your databases in Azure SQL. Check the version of your database with command:

SELECT name, compatibility_level FROM sys.databases;

Azure SQL Database Compatibility with SQL Server

The version of the SQL Server database engine run by Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics is always ahead of the on-premises version of SQL Server, and includes the latest security fixes. This means that the patch level is always on par with or ahead of the on-premises version of SQL Server, and that the latest features available in SQL Server are available in these services.

https://en.wikipedia.org/wiki/Microsoft_Azure_SQL_Database 

More Information:

https://database.guide/6-ways-to-check-the-size-of-a-database-in-sql-server-using-t-sql/ 

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server?view=azuresql

Product:

Microsoft Azure SQL

Issue:

Can not change default database for a SQL user in Azure SQL. User can not login to database, if it is not possible to select database in the login dialog ( like SSMS). You get this error when you try to change the default database for a SQL Azure user. If you create a SQL user that only resize in a single database, he can only login to that database. With Azure Data Studio can you select the database name during login dialog.

Solution:

Add the user to the master database too, with command:

create user pbi_reader for login pbi_reader

Before that, you may have run below command to created the login:

create login pbi_reader with password = 'Password!'

Switch to the user database, enter below code to create a user there:

create user pbi_reader for login pbi_reader

grant select on schema::DM to pbi_reader

Above command will give the SQL user pbi_reader access to the tables that have schema DM.

Azure SQL does not support the DEFAULT_DATABASE, DEFAULT_LANGUAGE, CHECK_EXPIRATION, CHECK_POLICY commands.

 

If you are db_owner in the SQL Azure database, you can add a Azure AD account to have access, by using a command like this:

CREATE USER [donald.duck@domain.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = DM;

To add a user as db_datareader to a specific database, use this command:

EXEC sp_addrolemember 'db_datareader', 'pbi_reader'

To list users in database, use this command:

SELECT * FROM SYS.DATABASE_PRINCIPALS

To be able to connect from Power BI to Azure SQL, you need to open the firewall in the database.

https://www.mssqltips.com/sqlservertip/5953/create-power-bi-connection-to-azure-sql-database/

https://hevodata.com/learn/azure-to-power-bi/

Power BI in the cloud, uses a span of ip address that change every week, if your database in the cloud or on-prem need to whitelist ip addresses in the firewall to gain access, it is best to eiter try to use a URL/DOMAIN in the firewall to allow PowerBI in the cloud to have access. You can create a support ticket with Microsoft Azure team and get a list of IP addresses that is used by Power BI.

For a on-prem database, the best solution is to install Azure gateway on-prem, then this box will keep the connection between on-prem and the azure based power bi service. And your on-prem databases will talk to the on-prem azure gateway.

The on-premises data gateway acts as a bridge. It provides quick and secure data transfer between on-premises data, which is data that isn’t in the cloud, and several Microsoft cloud services.

For Azure SQL in the cloud, it can be simpler to allow all cloud based services access, by inside https://portal.azure.com go to your SQL server page, then to network, and mark Allow Azure services and resources to access this workspace to be enabled. This could make it possible for Power BI in the cloud to access your Azure SQL database.

You may also be able to set Azure SQL firewall with command like this (in SSMS):

 exec sp_set_firewall_rule N'Allow DB Connections', '10.0.0.2', '10.0.0.2';

 

More information:

https://www.mssqltips.com/sqlservertip/5953/create-power-bi-connection-to-azure-sql-database/ 

https://www.microsoft.com/en-us/download/details.aspx?id=56519 

https://learn.microsoft.com/en-us/power-bi/admin/power-bi-allow-list-urls 

https://radacad.com/the-power-bi-gateway-all-you-need-to-know

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-gateway

https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem

https://chartio.com/learn/databases/grant-sql-server-table-permissions/

https://www.guru99.com/sql-server-create-user.html

Azure SQL Limitations compared with a SQL Server Enterprise

https://learn.microsoft.com/en-us/sql/azure-data-studio/quickstart-sql-server?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/azure-data-studio/quickstart-sql-database?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16&tabs=redhat-install%2Credhat-uninstall#install-azure-data-studio

https://devblogs.microsoft.com/premier-developer/secure-access-to-azure-sql-servers-for-power-bi/