Product:

Microsoft SQL Azure database

Issue:

A new created SQL native user [Kalle] can not see the tables in the database, but he can login to SSMS.

Solution:

Please do not use the role db_datareader or db_datawriter or their deny equivalents. They are for backwards compatibility only.

Remove the user from the role with below command, did not help.

EXEC sp_droprolemember 'db_datareader', 'Kalle'

You have to drop the user and create him again;

DROP USER Kalle

DROP LOGIN Kalle

Use Master

CREATE LOGIN Kalle WITH PASSWORD = 'advancedpasswordhere'

CREATE USER Kalle FOR LOGIN Kalle
-- to be able to login from SSMS you need to have the user in master database --

CREATE USER Kalle FOR LOGIN Kalle

-- gives the user Kalle access to see all tables in the DM schema --

GRANT SELECT ON SCHEMA::DM TO Kalle

This should give that the user only have read access to all tables and views that are part of the DM schema in the database.

To list members of built in roles use:

 SELECT DP1.name AS DatabaseRoleName, 
isnull (DP2.name, 'No members') AS DatabaseUserName 
FROM sys.database_role_members AS DRM 
RIGHT OUTER JOIN sys.database_principals AS DP1 
ON DRM.role_principal_id = DP1.principal_id 
LEFT OUTER JOIN sys.database_principals AS DP2 
ON DRM.member_principal_id = DP2.principal_id 
WHERE DP1.type = 'R'
ORDER BY DP1.name;

To list if any user have DENY rights use:

SELECT l.name as grantee_name, p.state_desc, p.permission_name, o.name
FROM sys.database_permissions AS p JOIN sys.database_principals AS l 
ON p.grantee_principal_id = l.principal_id
JOIN sys.sysobjects O 
ON p.major_id = O.id 
WHERE p.state_desc ='DENY'

More information:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-droprolemember-transact-sql?view=sql-server-ver16 

https://blog.sqlauthority.com/2017/03/02/sql-server-unable-see-tables-objects-ssms/ 

https://www.mssqltips.com/sqlservertip/2385/unable-to-see-a-sql-server-table/

Product:

Microsoft AZURE file storage

Issue:

How upload a file to AZURE file storage?

Suggestion:

Download the AZURE STORAGE EXPLORER and install it.  https://learn.microsoft.com/en-us/azure/vs-azure-tools-storage-manage-with-storage-explorer?tabs=windows 

Connect to Azure with your account from inside Azure Storage Explorer.

Expand in the left to your file share.

Click on Upload icon on the right.

Find a example file and upload from the correct folder you want to upload files from.

Click upload and watch the program work.

When finish in lower right corner click on link : ‘Copy AzCopy Command to Clipboard’ next to the log message.

Paste this into NOTEPAD.

Edit the string, to be as you want it to be.

Download azcopy.exe to a folder like d:\script from https://learn.microsoft.com/en-us/azure/storage/common/storage-use-azcopy-v10 

Open a powershell command window.

Go to the d:\script folder.

Paste in your azcopy command from the notepad into powershell session, and it will copy the files you defined.

Maybe you can programatically change the powershell script for azcopy to use it from a schedule program.

More Information:

https://learn.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal 

https://www.51sec.org/2022/08/12/using-azcopy-to-migrate-local-files-to-azure-blob-storage/

https://learn.microsoft.com/en-us/azure/storage/common/storage-configure-connection-string 

https://www.sqlshack.com/getting-started-with-azure-storage-explorer/ 

https://learn.microsoft.com/en-us/azure/storage/blobs/quickstart-storage-explorer

https://youtu.be/owXHtmQLQNY

Product:

SQL AZURE server

Issue:

Can not connect to AZURE SQL server in SSMS.

Error like:

the server was not found – msg 110001

Solution:

You need a new DNS record to point to the server-name in azure (db.database.windows.net).

There are more than one DNS zone where you need to update DNS records for your resource (privatelink.database.windows.net).

If you get a different error that, the server is connected but refuse to allow your user, then try with some other login method like:

Azure Active Directory – integrated

Azure Active Directory with MFA

SQL Server authentication (native login)

The allowed login method change with from where you try to connect and how firewall/DNS is setup.

More information:

https://learn.microsoft.com/en-us/azure/private-link/private-endpoint-dns 

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-11001-database-engine-error?view=sql-server-ver16

https://learn.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues?view=azuresql

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

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-powershell 

Product:

Microsoft SQL server

Issue:

How to import a txt file to a table inside SQL server?

Solution:

Download the file you want to import to your laptop or computer where SSMS is installed.

Right click on your database and select Tasks – Import Flat File.

Select the csv file and enter the name of the new table

Set a primary key, and change any date formatted columns to string or correct date format.

Then you can check the new table inside SSMS.

To use a small set in a new table, create a new table first:

CREATE TABLE [dbo].[Company](
[index] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[Employess] [int] NULL
) ON [PRIMARY]
GO

Then to copy the data over to that new table use:

INSERT INTO [AdventureWorksDW2019].[dbo].[Company] ([index], [name], [country], [employess] )
SELECT [index], [name] , [country] , [Number_of_employees]
FROM [AdventureWorksDW2019].[dbo].[organizations] 
-- [WHERE condition];

Use [  ] around columns names to ensure they are not misunderstood.

https://www.geeksforgeeks.org/how-to-use-reserved-words-as-column-names-in-sql/

More Information:

https://www.sqlshack.com/import-flat-file-sql-server-database-using-import-flat-file-wizard/

https://www.sqlservergeeks.com/sql-server-import-flat-file-using-ssms/

You can get sample data from this sites:

https://www.stats.govt.nz/large-datasets/csv-files-for-download/

https://people.sc.fsu.edu/~jburkardt/data/csv/csv.html

https://www.datablist.com/learn/csv/download-sample-csv-files

https://www.tutorialspoint.com/sql/sql-insert-query.htm

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