Generate Password Procedure

Feb 8, 2007

Stored procedure P_GENERATE_PASSWORDS returns a list of randomly generated passwords designed to meet typical password complexity requirements of a minimum of 8 characters, with at least one each of uppercase letters, lowercase letters, numbers, and special characters. It can generate from 1 to 10,000 passwords as a result set.

The passwords are meant to be somewhat mnemonic by generating syllables consisting of an uppercase consonant, followed by a lower case vowel, and a lowercase consonant. A single number or special character separates syllables, except in the case of 2 syllables. If there are only 2 syllables, they will be separated by a number and a special character.

Input parameters @SYLLABLE_COUNT and @PASSWORD_COUNT determine the password length and the number of passwords.





if objectproperty(object_id('dbo.P_GENERATE_PASSWORDS'),'IsProcedure') = 1
begin drop procedure dbo.P_GENERATE_PASSWORDS end
go
create procedure dbo.P_GENERATE_PASSWORDS
(
@SYLLABLE_COUNTint = null ,
@PASSWORD_COUNTint= null ,
@PASSWORD_STRENGTHfloat= nulloutput
)
as

/*
Procedure Name: P_GENERATE_PASSWORDS


Procedure Description:

P_GENERATE_PASSWORDS returns a list of randomly generated passwords
designed to meet typical password complexity requirements of a minimum
of 8 characters, with at least one each of uppercase letters,
lowercase letters, numbers, and special characters.

The passwords are meant to be somewhat mnemonic by generating
syllables consisting of an uppercase consonant, followed by a
lower case vowel, and a lowercase consonant. Syllables are separated
by a single number or special character, except in the case of 2 syllables.
If there are only 2 syllables, the syllables will be separated by
a number and a special character.

Passwords can be from 2 to 8 syllables in length.

Input parameter @SYLLABLE_COUNT is the total syllables in each output password.
The value of @SYLLABLE_COUNT must be between 2 and 8. If it is < 2 or null,
it is set to 3. If it is > 8 it is set to 8.

Input parameter @PASSWORD_COUNT is the total passwords to be returned.
The value of @SYLLABLE_COUNT must be between 1 and 10,000.
If it is < 1, it is set to 1. If it is null, it is set to 10.
If it is > 10,000 it is set to 10,000.

Output parameter @PASSWORD_STRENGTH returns the total possible
passwords that are possible for the selected @SYLLABLE_COUNT.

*/

set nocount on


-- Set password syllable count
set @SYLLABLE_COUNT =
case
when @SYLLABLE_COUNT is null
then 3
when @SYLLABLE_COUNT < 2
then 3
when @SYLLABLE_COUNT > 8
then 8
else @SYLLABLE_COUNT
end

-- Set password count
set @PASSWORD_COUNT =
case
when @PASSWORD_COUNT is null
then 10
when @PASSWORD_COUNT < 1
then 1
when @PASSWORD_COUNT > 10000
then 10000
else @PASSWORD_COUNT
end

declare @con varchar(200)
declare @vowel varchar(200)
declare @special varchar(200)
declare @num varchar(200)
declare @special_only varchar(200)
declare @con_len int
declare @vowel_len int
declare @special_len int
declare @num_len int
declare @special_only_len int
declare @strings int

-- set character strings for password generation
select
@con= 'bcdfghjklmnpqrstvwxyz',
@vowel= 'aeiou',
@num= '1234567890',
@special_only= '~!@#$%^&*()_+-={}|[]:;<>?,./'

set @special = @num+@special_only

-- set string lengths
select@con_len= len(@con),
@vowel_len= len(@vowel),
@special_len= len(@special),
@num_len= len(@num),
@special_only_len= len(@special_only) ,
@strings =
case
when @SYLLABLE_COUNT < 3
then 2
else @SYLLABLE_COUNT-1
end

--select @con, @vowel, @special, @num, @special_only,
--SELECT @con_len, @vowel_len, @special_len, @num, @special_only_len, @strings

-- Declare number tables to generate rows
declare @num1 table (NUMBER int not null primary key clustered)
declare @num2 table (NUMBER int not null primary key clustered)
declare @num3 table (NUMBER int not null primary key clustered)

declare @rows_needed_root int
set @rows_needed_root = convert(int,ceiling(sqrt(@PASSWORD_COUNT)))

-- Load number 0 to 16
insert into @num1 (NUMBER)
select 0 union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9
order by 1

-- Load table with numbers zero thru square root of the number of rows needed +1
insert into @num2 (NUMBER)
select
NUMBER = a.NUMBER+(10*b.NUMBER)
from
@num1 a cross join @num1 b
where
a.NUMBER+(10*b.NUMBER) <
@rows_needed_root
order by
1

-- Load table with the number of passwords needed
insert into @num3 (NUMBER)
select
NUMBER = a.NUMBER+(@rows_needed_root*b.NUMBER)
from
@num2 a
cross join
@num2 b
where
a.NUMBER+(@rows_needed_root*b.NUMBER) < @PASSWORD_COUNT
order by
1

-- Declare password string table
declare @p table (
numberintnot null
primary key clustered,
m1 varchar(10)not null,
m2 varchar(10)not null,
m3 varchar(10)not null,
m4 varchar(10)not null,
m5 varchar(10)not null,
m6 varchar(10)not null,
m7 varchar(10)not null,
m8 varchar(10)not null,

s1 varchar(10)not null,
s2 varchar(10)not null,
s3 varchar(10)not null,
s4 varchar(10)not null,
s5 varchar(10)not null,
s6 varchar(10)not null,
s7 varchar(10)not null
)

insert into @p
select
NUMBER,
-- M1 through M8 will be syllables composed of a single randomly selected
-- uppercase consonant, a single randomly selected lowercase vowel,
-- followed by as single randomly selected lowercase consonant.
m1 =
upper(substring(@con, (R11%@con_len)+1,1))+
substring(@vowel,(R12%@vowel_len)+1,1)+
substring(@con, (R13%@con_len)+1,1),
m2 =
upper(substring(@con, (R21%@con_len)+1,1))+
substring(@vowel,(R22%@vowel_len)+1,1)+
substring(@con, (R23%@con_len)+1,1),
m3 =
upper(substring(@con, (R31%@con_len)+1,1))+
substring(@vowel,(R32%@vowel_len)+1,1)+
substring(@con, (R33%@con_len)+1,1),
m4 =
upper(substring(@con, (R41%@con_len)+1,1))+
substring(@vowel,(R42%@vowel_len)+1,1)+
substring(@con, (R43%@con_len)+1,1),
m5 =
upper(substring(@con, (R51%@con_len)+1,1))+
substring(@vowel,(R52%@vowel_len)+1,1)+
substring(@con, (R53%@con_len)+1,1),
m6 =
upper(substring(@con, (R61%@con_len)+1,1))+
substring(@vowel,(R62%@vowel_len)+1,1)+
substring(@con, (R63%@con_len)+1,1),
m7 =
upper(substring(@con, (R71%@con_len)+1,1))+
substring(@vowel,(R72%@vowel_len)+1,1)+
substring(@con, (R73%@con_len)+1,1),
m8 =
upper(substring(@con, (R81%@con_len)+1,1))+
substring(@vowel,(R82%@vowel_len)+1,1)+
substring(@con, (R83%@con_len)+1,1),

-- S1 through S7 will each be a single randomly selected
-- number or special character. At least one of the used
-- columns will be a number and one will be a special character.

s1 =
case
when NUMBER_COL = 1
then substring(@num,(RS1%@num_len)+1,1)
when SPECIAL_COL = 1
then substring(@special_only,(RS1%@special_only_len)+1,1)
else substring(@special,(RS1%@special_len)+1,1)
end,
s2 =
case
when NUMBER_COL = 2
then substring(@num,(RS2%@num_len)+1,1)
when SPECIAL_COL = 2
then substring(@special_only,(RS2%@special_only_len)+1,1)
else substring(@special,(RS2%@special_len)+1,1)
end,
s3 =
case
when NUMBER_COL = 3
then substring(@num,(RS3%@num_len)+1,1)
when SPECIAL_COL = 3
then substring(@special_only,(RS3%@special_only_len)+1,1)
else substring(@special,(RS3%@special_len)+1,1)
end,
s4 =
case
when NUMBER_COL = 4
then substring(@num,(RS4%@num_len)+1,1)
when SPECIAL_COL = 4
then substring(@special_only,(RS4%@special_only_len)+1,1)
else substring(@special,(RS4%@special_len)+1,1)
end,
s5 =
case
when NUMBER_COL = 5
then substring(@num,(RS5%@num_len)+1,1)
when SPECIAL_COL = 5
then substring(@special_only,(RS5%@special_only_len)+1,1)
else substring(@special,(RS5%@special_len)+1,1)
end,
s6 =
case
when NUMBER_COL = 6
then substring(@num,(RS6%@num_len)+1,1)
when SPECIAL_COL = 6
then substring(@special_only,(RS6%@special_only_len)+1,1)
else substring(@special,(RS6%@special_len)+1,1)
end,
s7 =
case
when NUMBER_COL = 7
then substring(@num,(RS7%@num_len)+1,1)
when SPECIAL_COL = 7
then substring(@special_only,(RS7%@special_only_len)+1,1)
else substring(@special,(RS7%@special_len)+1,1)
end
from
(
select
aaaa.*,
-- Select random columns numbers to force at least
-- one special character and one number character
-- in each password
NUMBER_COL = (X1%@strings)+1 ,
SPECIAL_COL = ((((X2%(@strings-1))+1)+X1)%@strings)+1
from
(
select top 100 percent
NUMBER,
-- Generate random numbers for password generation
R11 = abs(convert(bigint,convert(varbinary(20),newid()))),
R12 = abs(convert(bigint,convert(varbinary(20),newid()))),
R13 = abs(convert(bigint,convert(varbinary(20),newid()))),
R21 = abs(convert(bigint,convert(varbinary(20),newid()))),
R22 = abs(convert(bigint,convert(varbinary(20),newid()))),
R23 = abs(convert(bigint,convert(varbinary(20),newid()))),
R31 = abs(convert(bigint,convert(varbinary(20),newid()))),
R32 = abs(convert(bigint,convert(varbinary(20),newid()))),
R33 = abs(convert(bigint,convert(varbinary(20),newid()))),
R41 = abs(convert(bigint,convert(varbinary(20),newid()))),
R42 = abs(convert(bigint,convert(varbinary(20),newid()))),
R43 = abs(convert(bigint,convert(varbinary(20),newid()))),
R51 = abs(convert(bigint,convert(varbinary(20),newid()))),
R52 = abs(convert(bigint,convert(varbinary(20),newid()))),
R53 = abs(convert(bigint,convert(varbinary(20),newid()))),
R61 = abs(convert(bigint,convert(varbinary(20),newid()))),
R62 = abs(convert(bigint,convert(varbinary(20),newid()))),
R63 = abs(convert(bigint,convert(varbinary(20),newid()))),
R71 = abs(convert(bigint,convert(varbinary(20),newid()))),
R72 = abs(convert(bigint,convert(varbinary(20),newid()))),
R73 = abs(convert(bigint,convert(varbinary(20),newid()))),
R81 = abs(convert(bigint,convert(varbinary(20),newid()))),
R82 = abs(convert(bigint,convert(varbinary(20),newid()))),
R83 = abs(convert(bigint,convert(varbinary(20),newid()))),

RS1 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS2 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS3 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS4 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS5 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS6 = abs(convert(bigint,convert(varbinary(20),newid()))),
RS7 = abs(convert(bigint,convert(varbinary(20),newid()))),

X1 = convert(bigint,abs(convert(int,convert(varbinary(20),newid())))),
X2 = convert(bigint,abs(convert(int,convert(varbinary(20),newid()))))
from
@num3 aaaaa

order by
aaaaa.NUMBER
) aaaa ) aaa
order by
aaa.NUMBER

-- Compute password strength as the total possible passwords
-- for the selected number of syllables.
select
@PASSWORD_STRENGTH =
power((@con_len*@con_len*@vowel_len)*1E,@SYLLABLE_COUNT*1E)*
(@special_only_len*@num_len*1E)*
case
when @strings < 3
then 1E
else power(@special_len*1E,(@strings-2)*1E)
end

-- Declare output table
declare @PASSWORD table
(
NUMBER intnot null
identity(1,1) primary key clustered,
[PASSWORD]varchar(32)not null
)

insert into @password ([PASSWORD])
selecttop 100 percent
[PASSWORD]
from
(
select
distinct
[PASSWORD] =
convert(varchar(32),
case
when @SYLLABLE_COUNT = 2
then m1+s1+s2+m2
else
substring(m1+s1+m2+s2+m3+s3+m4+s4+m5+s5+m6+s6+m7+s7+m8
,1,(@SYLLABLE_COUNT*4)-1)
end)
from @P
) a
where
-- Verify at least one number in password
[PASSWORD] like '%[1234567890]%'and
-- Verify at least one special character in password
[PASSWORD] like '%[^a-z1234567890]%'
order by
newid()

select * from @password order by NUMBER

return 0
go
grant execute on dbo.P_GENERATE_PASSWORDS to public

go

-- Test Script
declare @SYLLABLE_COUNTint
declare @PASSWORD_COUNTint
declare @PASSWORD_STRENGTHfloat

select @SYLLABLE_COUNT = 2 , @PASSWORD_COUNT = 5
print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


select @SYLLABLE_COUNT = 3 , @PASSWORD_COUNT = 6
print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


select @SYLLABLE_COUNT = 5 , @PASSWORD_COUNT = 7
print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


select @SYLLABLE_COUNT = 8 , @PASSWORD_COUNT = 20
print '@SYLLABLE_COUNT = '+convert(varchar(20),@SYLLABLE_COUNT)+
', @PASSWORD_COUNT = '+convert(varchar(20),@PASSWORD_COUNT)

exec dbo.P_GENERATE_PASSWORDS
@SYLLABLE_COUNT,@PASSWORD_COUNT,@PASSWORD_STRENGTH output

print '@PASSWORD_STRENGTH = '+convert(varchar(50),@PASSWORD_STRENGTH)
print ''


Results of Test Script:

@SYLLABLE_COUNT = 2, @PASSWORD_COUNT = 5
NUMBER PASSWORD
----------- --------------------------------
1 Tis|2Fun
2 Miy5]Fib
3 Bay1|Puz
4 Tel3.Pus
5 Duq0@Roy

@PASSWORD_STRENGTH = 1.40999e+009

@SYLLABLE_COUNT = 3, @PASSWORD_COUNT = 6
NUMBER PASSWORD
----------- --------------------------------
1 Qab@Kaz0Lan
2 Sav1Tig]Hat
3 Pah6Fic|Cic
4 Buz7Viz=Mec
5 Vig^Wah9Xuf
6 Qew2Mif^Mix

@PASSWORD_STRENGTH = 3.10902e+012

@SYLLABLE_COUNT = 5, @PASSWORD_COUNT = 7
NUMBER PASSWORD
----------- --------------------------------
1 Mux4Zor_Jog{Vec,Bih
2 Ker1Qem[Gat,Hut|Zif
3 Red}Ciq5Ber%Son:Qej
4 Cov@Doz8ZowFic>Pos
5 Tad0Bek&Fug_Kiv9Rez
6 Pil1Nul$Vil~Koh_Xel
7 Zuk4Gir&Yep|Ned)Sap

@PASSWORD_STRENGTH = 2.29917e+022

@SYLLABLE_COUNT = 8, @PASSWORD_COUNT = 20
NUMBER PASSWORD
----------- --------------------------------
1 Biz&Xak9Gew{Vuf[Tix;Qap-Bik{Vay
2 Rof<Job*Fax-Niq/Zew9Pah:Bag(Zok
3 Noh1Nor7Rul5Fon@Mig>Xod.Lay.Maq
4 Piw:Keb}Rod8Yah}VawLet@Yoq9Sav
5 Hav@Qer/Met7Zig&Jiw4Pot-Fod(Zat
6 Bid_Lal+Bay3Fos9FezFaw!Kad4Zok
7 Qar-Kig-Lem3Yeq?Xuj7Zun,Xid=Xel
8 Biq6Jot:Caj(Xun2Kup[Fax|Gec,Xon
9 Yac7Nox^Woy~Wag0XanHil3Cab/Nit
10 Pod+Kor%Fov7Vil,Dor:Xoq!Kel3Poq
11 Goc)Roz7Ruq/Pad8Jeh*Xaj&Dew{Duy
12 Sik/Ruj@Wiv9Qik[Sub=Qim,Ned:Qit
13 Les9Har&Ceb5Heg^Fov0Vaf1Fuf[Maq
14 Deg6Yiw$Peg:Wuj7Woc_Mip|Kam9Zus
15 Nix^Dev%Qoj=Seq[Jig6Lig}Day-Ric
16 Dux;Woy=Zud1Mak5Yej$Kav2Mek5Buh
17 Yuv8Mor9Wix&Giq5Zar@Nuk$Pey<Lok
18 Dem~Kof-Yoq(Xig$TewFun7Meq2Kik
19 Caq1Qag{Pes{Gex|Til=Vuk7Tig1Vur
20 Miw)Law}Tun2Lop.Jix#Riq|Yat$Juc

@PASSWORD_STRENGTH = 1.46214e+037







CODO ERGO SUM

View 15 Replies


ADVERTISEMENT

SQL 2012 :: Cannot Generate SSPI Context Error When Password Changes

May 22, 2013

I regularly (every month or so) get the error "The target principle name is incorrect. Cannot generate SSPI context" when trying to remotely connect to my SQL 2012 instance. The SQL service is running using a managed service account. I understand this error can occur when the service account cannot authenticate with AD properly. Looking at the properties of the managed service account, the password for the account was automatically changed this morning - just when the error started.

View 9 Replies View Related

Generate Store Procedure ?

Feb 6, 2006

I have a table in database !I want to generate store procedure from that table by using tool of SQL server 2000( which include some statements : insert,delete, .....)how can i do that ? thank you very much .

View 1 Replies View Related

Generate SQL Script - Stored Procedure

Jan 31, 2007

In SQL 2000 version, we can select all the Stored Procedure function and export out all the script.

But in 2005 version, how it can be done?

Thanks

View 13 Replies View Related

Generate XML File From Stored Procedure

Jul 20, 2005

I need to develop some crystal reports on some .NET ado datasets.This is easy to do if I actually had data to work with. It is mucheaser creating reports with you have data.Therefore, I would like to run the stored procedure that the .NET adodataset is built on and generate an XML file. I could run the projectand try to figure out where the developers are making the call to thestored procedure and insert a line to writetoxmlfile. I would rathernot have to mess with their code.Is there a way working with SQL Server (either query analyzer orenterprise manager, dts, or whatever) that I can generate an xml file.I see that I can run a stored procedure and get an xml style return inquery analyzer, but I don't know how to save that as an actual file.Thanks for the help.Tony

View 3 Replies View Related

Generate Script Of A Procedure Without Using Enterprise Manager

Jan 29, 2002

Hi All,
I know we could get the script of a stored procedure in Enterprise Manager. However I need to do this using a utility in dos prompt. I need to specity a stored procedure and script out to an output file.
Is this possible?
Thank you!

View 7 Replies View Related

Stored Procedure To Generate Custom ID For Each Asset

Jan 7, 2012

I need creating a store procedure which generates custom IDs for each asset. I am programming for Fixed Assets in VB6 with SQL Server 2005. For example, when a new Asset is added ,I need to auto generate the ID based on existing IDs. New ID should not exist in tblAssets table.

Table Name : tblAssets
Fields : AssetID > Int,Primary Key,this is internal ID (identity seed)
AssetExtID >nvarchar(50),this is external ID, need to generate/user entered.

Below is the example of data in tblAssets :

AssetID AssetExtID ProjectID ItemName Qty UOM UnitCost .....
1 PROSP-00001 PROSPERITY SPLIT-AC 2 NOS $200
2 PROSP-00002 PROSPERITY LAPTOP 1 NOS $500
3 UNIII-00001 UNION III LAPTOP 5 NOS $400
4 UNIII-00002 UNION III RECEIVER 2 NOS $312

The AssetExtID depends on the ProjectID which is in tblProjects.

I will take care of the first 5 characters to generate. But the number part I need to generate by checking existing data. The AssetExtID should not be duplicate. Its unique for each asset.

View 4 Replies View Related

SQL Server 2012 :: Generate PDF From Stored Procedure

Mar 3, 2015

We need to create a pdf file from SQL server preferably from a stored procedure. Application will call the stored procedure and it should generate pdf. From my research it appears it can be done using various external tools with licensing/costs. But is it possible to do this within sql server database without additional costs? I read that this can be done by SSRS in SQL server but not sure if it is a good solution and if it is additional licensing..

View 3 Replies View Related

Generate Flat File Via Stored Procedure

Oct 10, 2006

I have a need to do the following:

Generate a Stored Procedure and have the output written in a csv format.

I have everything I need to capture the data via stored procedure, but I am lost on a way to 'INSERT' the data values into a csv file.

This stored procedure will be triggered by another application.

Could someone please help.

thanks

View 4 Replies View Related

Create Procedure Or Trigger To Auto Generate String ID

Feb 20, 2004

Dear everyone,

I would like to create auto-generated "string" ID for any new record inserted in SQL Server 2000.

I have found some SQL Server 2000 book. But it does not cover how to create procedure or trigger to generate auto ID in the string format.

Could anyone know how to do that?? Thanks!!

From,

Roy

View 7 Replies View Related

Generate Separate Script For Each Stored Procedure Automaticaly

Oct 3, 2001

There is SCPTXFR.EXE script that has an option to create a script for all DB objects in one file or create separate files for each DB object - one for tables, one for SP-s, one for triggers, etc.

Is there a way to generate:

1. Separate script for each stored procedure (not from Enterprise Manager but automatically from command line or otherwise);

2. Script all DB objects excluding stored procedures in one file.

Thank you.

View 3 Replies View Related

MS SQL Server 2005: Collect Procedure For Dts Pipeline Generate Error

Nov 21, 2006

Dear experts,

My MS SQL Server 2005 is generating the following error. may i know what's wrong with it?

"
The Collect Procedure for the "DTSPipeline" service in DLL "XXX:Program FilesMicrosoft SQL Server (x86)90DTSBinnDTSPipelinePerf.dll" generated an exception or returned an invalid status. Performance data returned by counter DLL will be not be returned in Perf Data Block. The exception or status code returned is the first DWORD in the attached data.
"

Thanks in advance for any assistance rendered.
pat

View 6 Replies View Related

System Stored Procedure Name That Allows One To Change One's Password ?

Jan 2, 2008

I am using a web application using asp.net 1.1 vs 2003.
i am using sql server authentication where users are using their sql server user id and sql server saved passwords.
how can i change their passwords inside the sql server?
is there a system sp that allows one to pass one's user id and then change password.
 I will need to call that sp from a user defined store dprocedure and pass the parameters and that will change the password on the sql server.
thanks
  
 

View 3 Replies View Related

Passing Key Decryption Password To Stored Procedure

Aug 7, 2006

I try to pass the key used to decrypt symmetric key to a stored procedure as a parameter like this:

OPEN SYMMETRIC KEY MyKey
DECRYPTION BY PASSWORD=@ keypassword;

I get an error message saying "Incorrect syntax near "@keypassword".
Is there something that I am missing?

View 1 Replies View Related

Login With A Case Sensitive Password With A Stored Procedure

Nov 4, 2003

I have a stored procedure that validates a user login against a username and password field.
How can I ensure case sensitivity in the stored procedure for the password field?

View 4 Replies View Related

SQL Server 2012 :: How To Create Password On Stored Procedure

Jun 27, 2014

is it possible to create PW on Stored Procedure? No one can execute or Alter any Store Procedure with Password?

View 1 Replies View Related

Data Access :: How To Recover Forgotten Password When Remember Password Option Checked

Jul 24, 2015

I have connected to Database using my credentials by checking remember password option. After few days I forgot my password. How can I recover the password as SQL remember it. Is there any way to recover my password instead of resetting it.

View 3 Replies View Related

SQL Server Agent Job Will Not Retain Package Password (encrypt Sensitive With Password)

Apr 1, 2008

I have a package protected by a password - I am already unhappy that to get it to use the configuration file to change connection strings for the production servers I have had to hardcode the password into the config file - very insecure!
However, the package now deploys correctly to the production server and will run from there OK, but NOT if scheduled as a SQL Server Agent Job. Thus is because however often I edit the command line to include the password after the DECRYPT switch (which it has prompted me for when I click on the command line tab), the Job Step will not retain it.
If I open it up after I have edited it and closed it, the password has disappeared.

I know that if I run dtexec plus the code in the Command Line tab (with the password), the package runs OK.

This is driving me insane!
I have read all the other posts and so I tried replacing the SSIS package step with a CmdExec step and pasting that code into there - then I get an OLEDB error..

The code I use is:
DTEXEC /SQL "ImportRateMonitoringTables" /SERVER servername /DECRYPT password /CONFIGFILE "D:Microsoft SQL ServerSSISDeploymentsRateMonitoringImportTasksDeploymentImportRateMonitoringTables_Production.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

and I get

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF

although the same code executes perfectly from a command prompt.

Please does anyone have any experience with a similar problem and if so, how did you get round it?

Thank you

View 9 Replies View Related

SQL 2005 SP2 - Windows 2003 Ent SP1 - Password Does Not Meet The Password Policy DLL

Jun 18, 2007

I am receiving the following error message when attempting to create a new SQL Authenticated login id.



Password validation failed. The password does not meet the requirements of the password filter DLL. (Microsoft SQL Server, Error: 15119)



I have four servers all running SQL Server 2005 SP2 on Windows 2003 Ent. SP1. Of the four servers, only one received the above error message using the same TSQL below.



CREATE LOGIN TEST_LOGIN WITH PASSWORD = 'pvif9dal' MUST_CHANGE, CHECK_EXPIRATION = ON



All four servers are in the same domain, which if I understand correctly, the password policies are therefore inherited at the OS level by the domain. The password being used is within the password policies of the domain.



Any ideas as to a root cause?

View 5 Replies View Related

The Sa Password Must Meet SQL Server Password Policy Requirements.

Jun 30, 2007

I tried to install an ALLDATA database which run with SQL Server 2005 express edition. The data base fails to install becase of the following code that come up which is related to AS password requirement. The error that come up is:



TITLE: Microsoft SQL Server 2005 Setup
------------------------------

The sa password must meet SQL Server password policy requirements. For strong password guidelines, see Authentication Mode, in SQL Server Books Online.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=setup.rll&EvtID=28001&EvtType=sqlca%5csqlcax.cpp%40SAPasswordPolicyCheck%40SAPasswordPolicyCheck%40x6d61

------------------------------
BUTTONS:

&Retry
Cancel
------------------------------


I am trying to install this database in a network server operating under Windows Server 2003 R2 with SP2. If anyone knows how to solve this problem, please let me.



Thanks,



Amilcar

View 6 Replies View Related

Error Message: Failed To Generate A User Instance Of SQL Server. Only An Integrated Connection Can Generate A User Instance

Mar 3, 2008

 Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring  the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the  SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf).          Plz enlighten me on this.Thanks and Regards,Sankar. 

View 1 Replies View Related

Remember My Password Is Forgetting Password

Mar 11, 2008

On the login prompt for Report Server, there is a checkbox option to "Remember my Password." I check this, login successfully... but when returning to the Report Server, I am again prompted for the password, although the user name is saved. No matter how many times I do this, the password will not save in IE7. I have tried this on 3 different computers with IE7. In IE 6, I do not even get the checkbox as an option, just the user name/password prompt. In Firefox, the password saves fine. Any ideas what would be causing this?

Thanks,

Brian

View 1 Replies View Related

Export Username / Password To CSV File To Test SP To Output Username / Password

Jun 2, 2014

I put this together to export the user name /password to a csv file to test my SP to output the user name/password.

DECLARE @user_name varchar(50)
DECLARE @psswrd varchar(10)
SELECT @user_name ,@psswrd
FROM ngweb_bulk_enrollments
EXEC master.dbo.xp_cmdshell 'bcp NGDevl.dbo.ngweb_bulk_enrollments out C: est.csv -Sserver1 -T -t, -r
-c'

This works but I don't get the headers in the file. How can I include the headers?

View 7 Replies View Related

Equivalent To MySQL's Password() Function? (was MySQL To SQL Server And Password())

Mar 3, 2005

I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this:


PHP Code:




 $sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")"; 






Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.

View 7 Replies View Related

Generate .sql ?

Jan 9, 2007

Hi!
When MS published starter kits there were files .sql in App_Data. This files contained some sample data for a DB. How to create such files when I have database with data ?
Jarod

View 2 Replies View Related

Generate Xml

Oct 20, 2007

how generate an xml file from data in sqlserver2005
and read it in sql again

View 4 Replies View Related

Generate FKs

Nov 20, 2002

One of my first tasks on my current project was to generate all of the SQL scripts needed to rebuild the database. Of course, one of the catches was that everything needed to be separated out (table columns in different script from PKs different from FKs different from DEFAULTs, etc., etc.). And we wanted our own comment block inserted. So, I couldn't just use the Generate SQL Script from Enterprise Manager and be done with it. I was going to need to do some more work. Well, here's one of the scripts I wrote to build the FK scripts. Execute this script, then copy & paste the results into a new window. Break apart into separate .sql files as desired.

NOTE: Be sure to show results in Text (not grid) and change the display options in QA to return more than just 256 characters.


/***************************************************************************************************
Author: Mark Caldwell
Date: 11/15/2002
Descrip: Generate scipt commands to ADD FOREIGN KEY CONSTRAINTS for constraints already in DB.

NOTE: Be sure to set your Tools/Options/Results/Maximum Characters per column to a
large enough number (such as 2000) to display the entire command.
***************************************************************************************************/

SELECT
'----------------------------------------------------------------------------------------------------
-- ' + so2.name + ' to ' + so3.name + '
----------------------------------------------------------------------------------------------------
IF OBJECTPROPERTY(OBJECT_ID(N''' + so1.name + '''), ''IsForeignKey'') = 1 BEGIN
ALTER TABLE ' + so2.name + ' DROP CONSTRAINT ' + so1.name + '
PRINT '' -- DRP - ' + so1.name + '''
END
G' + 'O

ALTER TABLE ' + so2.name + '
ADD CONSTRAINT ' + so1.name + '
FOREIGN KEY (' + ISC1.COLUMN_NAME + ')
REFERENCES ' + so3.name + '(' + ISC2.COLUMN_NAME + ')

PRINT '' -- ADD - ' + so1.name + '''
G' + 'O

'
FROM sysforeignkeys sfk
JOIN sysobjects so1 on sfk.constid = so1.id
JOIN sysobjects so2 on sfk.fkeyid = so2.id
JOIN sysobjects so3 on sfk.rkeyid = so3.id
JOIN INFORMATION_SCHEMA.COLUMNS ISC1 on so2.name = ISC1.TABLE_NAME AND sfk.fkey = ISC1.ORDINAL_POSITION
JOIN INFORMATION_SCHEMA.COLUMNS ISC2 on so3.name = ISC2.TABLE_NAME AND sfk.rkey = ISC2.ORDINAL_POSITION
ORDER BY so2.name




Edited by - AjarnMark on 11/20/2002 02:46:11

View 1 Replies View Related

Generate Sql Script?

Oct 22, 2006

The web site I am building is working fine locally, but I am hitting some problems with setting it up on a remote hosting server.First off, how can I generate the sql script to populate the SQL db on the remote server?I am using VS 2005 Standard. Do I need to d/l the SQL Server Express?Once I get that going, I should be able to figure out the rest...but I'll prolly have another question or two.Thanks

View 2 Replies View Related

How To Generate Userid?

Feb 15, 2008

Hello everyone.

i need to auto generate the user id in id colunm in my sqldatabase table.i want it to generate in this fashion.(mycompanyname-todaydate-number.)eg (ibm-15thfeb-1)    (ibm-15thfeb-2)       (ibm-16thfeb-1)here i need this user id to be automatically displayed in my web form when doing registration of new user,then only after clicking the savebutton i want all the data along with user id to be inserted into the table in sqldatabase.thanksjack.   

View 38 Replies View Related

Generate AutoNumber

Jul 16, 2005

Hi, I have a question, I have created a table and with a primary key called "ID". However, I want the "ID" be auto increment as well. when inserting new record into the database.I'm using vb.net. how can I do in the following format: "1", "2", "3", ............ etc. I've the code below but it's not working in the right way, what's wrong with my code?
Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim ssql As String
Dim Itemid As Integer
Dim updcmd As SqlClient.SqlCommand
Itemid = 0
mysqladap = New SqlClient.SqlDataAdapter("select MAX(Item_id) From auction where item_type= '" & (Image1.ImageUrl) & "'", mySqlConn)
Itemid = (Itemid) + 1
ssql = "insert into auction (item_id,owner_name,owner_mail,owner_mobile,owner_phone,owner_ext,item_type,item_name,item_image,item_desc,item_cost,start_date) values ('" & (Itemid) & "','" & Trim(ownertxt.Text) & "' ,'" & Trim(emailtxt.Text) & "', '" & Trim(mobiletxt.Text) & "', '" & Trim(phonetxt.Text) & "','" & Trim(exttxt.Text) & "','" & Trim(DropDownList1.SelectedValue) & "','" & Trim(itemtxt.Text) & "','" & Trim(Image1.ImageUrl) & "','" & Trim(desctxt.Text) & "','" & Trim(costtxt.Text) & "','" & Trim(Today.Date) & "')"
updcmd = New SqlClient.SqlCommand(ssql, mySqlConn)
updcmd.ExecuteNonQuery()
lblmsg.Visible = True
End SubAnyone can help me? Thanks.

View 7 Replies View Related

&#39;Generate SQL Scripts&#39;

Apr 2, 2001

Are there any scripts available with anyone, for generating SQL scripts for all the objects in a server?

Thanks

View 1 Replies View Related

Generate Script

Apr 2, 2001

I want to transfer some procedures using a generated script. However, the procedures that create pre-existing tables will not be created. Is there a way to shut off the validation so that I can create these procedures without getting errors?


Thanks for the help

View 1 Replies View Related

DTS - Generate SQL Script

Jun 7, 2002

Is there a way to write a dts package that will Generate SQL Scripts for a particular database? I am just learning dts and would like to automate this process that I run on weekly basis.

Thanks,

Steve

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved