January 7, 2020 at 2:19 pm
All, I am in a process of implementing TDE on all DBs which are on our Dev servers. There are close to 200 DBs and I am creating 1 certificate for each DB. So there are going to be 200 certificates. I am also going to be backing up those certificate using different password. And this is where I am looking to get some help. Code is below
Create procedure [dbo].[GenPass]
AS
DECLARE @char CHAR = ''
DECLARE @charI INT = 0
DECLARE @password VARCHAR(100) = ''
DECLARE @len INT = 20 -- Length of Password
WHILE @len > 0
BEGIN
SET @charI = ROUND(RAND()*100,0)
SET @char = CHAR(@charI)
IF @charI > 48 AND @charI < 122
BEGIN
SET @password += @char
SET @len = @len - 1
END
END
SELECT @password [PassWord]
DECLARE @GenPass nvarchar(100)
DECLARE @sql VARCHAR(4000)
exec @GenPass = DBMAINT.dbo.GenPass --This is where I am having issues
DECLARE cur CURSOR
FOR
SELECT 'Backup CERTIFICATE TDE_' + name + '
to FILE = ''\\************\''
WITH PRIVATE KEY (file=''\\************\****.pvk'',
ENCRYPTION BY PASSWORD='''+@GenPass+''')
'
FROM sys.certificates
OPEN cur
FETCH NEXT FROM cur INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql
FETCH NEXT FROM cur INTO @sql
END
CLOSE cur
DEALLOCATE cur
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 7, 2020 at 3:11 pm
Why not just generate GUIDs? With the dashes, they're 36 bytes in length when converted to CHAR. Without the dashes, they're 32.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2020 at 2:21 am
The problem is your procedure is returning a result set, which can't be used to populate a variable. You need to use an OUTPUT variable instead, which can then extract the returned password that you can use later on in the script.
You can see a working example in this db<>fiddle (code below).
CREATE PROC NoOutputVar
AS
BEGIN
SELECT '123' AS [Output]
END
GO
DECLARE @Output VARCHAR(25)
-- Returns an output, because we have SELECT in the PROC
EXEC @Output = NoOutputVar
-- Output was not stored in the variable because no OUTPUT variable declared
SELECT @Output AS VariableValue
CREATE PROC OutputVar
(
@ProcOutput VARCHAR(25) OUTPUT
)
AS
BEGIN
SELECT @ProcOutput = '123'
END
GO
DECLARE @Output VARCHAR(25)
-- Returns no output as we're selecting INTO a variable
EXEC OutputVar @ProcOutput = @Output OUTPUT
-- Output was stored in the variable because we used OUTPUT variable
SELECT @Output AS VariableValue?
January 20, 2020 at 8:55 pm
This is the script I use when I need to generate passwords...
SET NOCOUNT ON;
GO
DECLARE
@pw_len TINYINT = 12,
@enforce_number BIT = 1,
@enforce_upper BIT = 1,
@enforce_lower BIT = 1,
@enforce_special BIT = 1,
@how_many INT = 5000;-- how many pw to generate (10,000 max)...
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_Tally (n) AS (
SELECT TOP ((SELECT MAX(x.rc) FROM (VALUES (@pw_len), (@how_many * 2)) x (rc)))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n2 a CROSS JOIN cte_n2 b
)
SELECT TOP (@how_many)
n= ROW_NUMBER() OVER (ORDER BY pw.n),
pw.password
FROM (
SELECT
hm.n,
password = STRING_AGG(pl.char_val, '')
FROM
(SELECT TOP (@how_many * 2) t.n FROM cte_Tally t) hm
CROSS JOIN (SELECT TOP (@pw_len) CHAR(ABS(CHECKSUM(NEWID())) % 94 + 33) FROM cte_Tally t) pl (char_val)
GROUP BY
hm.n
) pw
WHERE 1 = 1
AND (@enforce_number = 0 OR pw.password LIKE '%[0-9]%')
AND (@enforce_upper = 0 OR pw.password COLLATE Latin1_General_CS_AS LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%')
AND (@enforce_lower = 0 OR pw.password COLLATE Latin1_General_CS_AS LIKE '%[abcdefghijklmnopqrstuvwxyz]%')
AND (@enforce_special = 0 OR pw.password LIKE '%[^0-Z]%');
GO
Of course you're using SQL Server 2016 and the STRING_AGG function won't work for you. So here is a version that replaces it with "for xml path..."
SET NOCOUNT ON;
GO
DECLARE
@pw_len TINYINT = 12,
@enforce_number BIT = 1,
@enforce_upper BIT = 1,
@enforce_lower BIT = 1,
@enforce_special BIT = 1,
@how_many INT = 5000;-- how many pw to generate (10,000 max)...
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_Tally (n) AS (
SELECT TOP ((SELECT MAX(x.rc) FROM (VALUES (@pw_len), (@how_many * 2)) x (rc)))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n2 a CROSS JOIN cte_n2 b
)
SELECT TOP (@how_many)
n= ROW_NUMBER() OVER (ORDER BY pw.n),
pw.password
FROM (
SELECT
hm.n,
password = x.password
FROM
(SELECT TOP (@how_many * 2) t.n FROM cte_Tally t) hm
--CROSS JOIN (SELECT TOP (@pw_len) CHAR(ABS(CHECKSUM(NEWID())) % 94 + 33) FROM cte_Tally t) pl (char_val)
CROSS APPLY (
SELECT (
SELECT TOP (@pw_len)
'' + CHAR(ABS(CHECKSUM(CONCAT(NEWID(), hm.n))) % 94 + 33)
FROM
cte_Tally t
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
) x (password)
) pw
WHERE 1 = 1
AND (@enforce_number = 0 OR pw.password LIKE '%[0-9]%')
AND (@enforce_upper = 0 OR pw.password COLLATE Latin1_General_CS_AS LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%')
AND (@enforce_lower = 0 OR pw.password COLLATE Latin1_General_CS_AS LIKE '%[abcdefghijklmnopqrstuvwxyz]%')
AND (@enforce_special = 0 OR pw.password LIKE '%[^0-Z]%');
GO
Hope this helps.
Jason
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply