how to encrypt a single field like a password field

without writing code in my application? Does SQL Server have stored procedure to do it?
Any help is appreciated.
Thanks.SQL Server 2005 has support for encryption, but you have to manage keys, and write your own stored procedures that use the encrypt and decrypt functions. SQL 2000 does not have native support for encryption, I believe.|||Attached is function that is suitable for encrypting passwords.|||naaahh. any encryption formula you put together just is not going to do the job the public-key encryption is going to do.|||Attached is function that is suitable for encrypting passwords.
I have downloaded the code and will take a look at it.|||Theres an inbuilt function in SQL2000:

column type must be:
Declare PWCol varbinary(256)

to insert/update use:

...CONVERT(varbinary(256), PWDENCRYPT('THEPASSWORD'))

and to compare a password...

...where PWDCOMPARE('thepassword', PWCol) = 1

Cheers,
Phil
--
Always remember that you're unique, just like everyone else.|||a quick google search on this undocumented function gives you results on how to hack it. PUBLIC KEY ENCRYPTION is the safest bet. It's been a while since I have done this (4 years?) but I used the RSA cypher.|||naaahh. any encryption formula you put together just is not going to do the job the public-key encryption is going to do.
Not true. The encryption algorithm I gave is a "one-way" algorithm. It cannot be unencrypted, and thus is only suitable in limited situations such as password encryption. It is relatively easy to make secure one-way encryption schemes.
The challenge is to make a secure "two-way" encryption algorithm. SQL Server's built-in encryption is "two-way" but is not secure and was hacked years ago, and the decryption method is readily available on the web.|||Duplicate post.|||a quick google search on this undocumented function gives you results on how to hack it.

Blimey you boys do love to p!$$ on someones fire.

It's only hackable if your front end code is crap and you don't parse throu before SQL.
If you're stupid enough to leave your SQL server open to access then the fact you can hack a password in a table is pretty irrelevant when you can get control of the whole box.

Right, I'm off to sulk in the corner.

...

To err is human, to forgive is not our Policy.|||Whoa, Mr. Sensitive! You're gonna need thicker skin than that!

And no sulking, either. If you think we're full-o-crap, then just say so (but without throwing all tact to the wind...).

P!$$!ng on someone's fire: allowed.
Sulking in the corner: frowned upon.
P!$$!ing in the corner: well, when ya gotta go...|||Well at least I now know why my sulking corner is starting to smell so bad.|||We generally do our sulking and grousing in the Yak Corral. You can join us there:
http://www.dbforums.com/showthread.php?t=989246&page=289|||Wy not to use the in-built function encrypt() ?|||It is an undocumented function which may not be supported, or may use a different algorithm in future releases.

The algorithm has changed through releases in the past, rendering whole databases inaccessible for applications that relied upon it.

How to enable remote connections by code?

How to enable remote connections by code without "SQL Server Surface Area Configuration" ,implement by .net framework?

Thats pretty easy using SMO:

instance.ServerProtocols[protocol].IsEnabled = true;

Whereas protocol is the protocol you want to enable (like "tcp" or "np") ans instance is the server instance you want to configure. I wrote an application, the RemoteconnectionsEnabler which does all this using the SMO library.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to edit Stored Procedure from ASP.NET environment?

In visual studio 6.0, there are Database tools, including:
Data View
Database Designer
Query Designer
Source Code Editor for Stored Procedure and Triggers

From .NET environment, click Tools on menu bar --> Connect to Database, I can get the 'Data View', while click the table name in the Data View, I can get 'Qeury Designer'.

It seems the 'Database designer' and 'Source Code Editor' are missing.

Questions:
How can I edit, execute and deburg a stored procedure or trigger from within the .NET environment?

How can I create, modify a database, table and so on from .NET environment?

The most useful and important feature is from the question one. Thanks for any information.Hi,

Assuming you have an enterprise version of VS.NET, you can use the Server Explorer. It should be on the left side of the IDE, alongside the Toolbox. You can also select View | Server Explorer from the main menu.

Don|||Yes, I do have Server Explorer that is what I said the 'Data View' in Visual Studio 6.0. But, if you try to expand the database, and choose stored procedure, double click one of SP, you will see you cannot open it in the .NET environment.

In VS 6.0, if you right click a stored procedure, you will see several choices: open, execute, debug, new stored procedure and refresh.

But, in the server explorer, if you right click a stored procedure, you will only see run stored procedure and refresh.

It seems you have no way to edit a stored procedure. That should be the case, I believe, because this feature is quite useful and has been already integrated into VS 6.0.

By the way, I use pro version for .NET, but enterprise version for VS 6.0. Does it make any difference?|||Yep, that's why. As I mentioned, you need one of the enterprise versions to have these features. That's why you're not seeing them. And it's why youare seeing them in your enterprise edition of VB6.

Don|||That makes sense. Thanks, Don.|||Don, I have VS.Net 2003 Pro and can create and edit stored procs (and other objects) in the NetSDK instance of SQL Server on my PC but can not do this in the default instance, which contains my real databases. Do I need to move my databases to the NetSDK instance or upgrade to the Enterprise Edition to get the full functionality?|||Hi,

Hmm, I'm not sure about how each edition handles named instances. Hopefully someone else can chime in, although it's been a few days since you posted this.

Don|||What do you mean? Can you give an example?|||What do you mean? Can you give an example?

Are you asking that of me or dlgross?

Don|||Sorry, Don, the quesiton must be for dlgross.|||dlgross,

I have VS.Net 2003 Pro and can create and edit stored procs (and other objects) in the NetSDK instance of SQL Server on my PC

I have asked a question to you before, what did you mean? I have installed VS.NET 2003 Pro, but I didn't see the difference between VS.NET 2002 and 2003 for this issue. i.e., No way to edit Stored Procedure in .NET either 2002 or 2003 environment.

If you indeed can do it, could you show some example? I would rather like than "in VS .NET pro environment, Stored Proc cannot be edited" is a wrong statement?

How to edit a asp.net2.0/C# project in VS2005, Plz HELP

Hi,

How can i run or edit my ASP.NET 2.0/C# project in VS2005. I have source code files (including database) but it doesn't have .sln file. Please guide me how can i edit or run this in VS2005

Hi,

if you are missing the sln file, you should create a new web project and import the existing files to your solution.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the help.

which files are necessary, while sending ASP.NET 2.0/C# project (for further development) to the developer, like .sln files, database.

please write the name of other files. and how to open that project in VS2005.

Thanks

vkkv

|||Every file except *user files as they store user specific information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to do this the T-SQL way.

I have this code in VB...

OpenOrdSQL = "SELECT JPO, QtyDlv, OutStandVal FROM ActiveOrders WHERE JPO < '5000000' " & _
"ORDER BY JPO ASC"
Set OpenOrdRS = New ADODB.Recordset
OpenOrdRS.Open OpenOrdSQL, DB

Do Until OpenOrdRS.EOF
ActiveSQL = "SELECT * FROM fn_ActiveOrders('" & OpenOrdRS!JPO & "')"
Set ActiveRS = New ADODB.Recordset
ActiveRS.Open ActiveSQL, DB

If ActiveRS.EOF Then
SQL = "DELETE FROM ActiveOrders WHERE JPO = '" & OpenOrdRS!JPO & "'"
DB.Execute SQL
Else
If (Int(OpenOrdRS!QtyDlv) - Int(ActiveRS!QtyDlv)) Or (Int(OpenOrdRS!OutStandVal) - Int(ActiveRS!OpenVal)) Then
SQL = "UPDATE ActiveOrders SET OutStandVal = '" & ActiveRS!OpenVal & _
"', SQM = '" & ActiveRS!OpenSQM & "', QtyDlv = '" & ActiveRS!QtyDlv & _
"' WHERE JPO = '" & OpenOrdRS!JPO & "'"
DB.Execute SQL
End If
End If

OpenOrdRS.MoveNext
Loop

...that I'm trying to imitate in SQL server so that I can remove this module from my VB project and use SQL Server Agent. And this is what I've come up...

DECLARE @.JPO NUMERIC, @.OpenVal FLOAT, @.OpenSQM FLOAT, @.OpenQty NUMERIC
SELECT @.JPO = JPO,
QtyDlv,
OutStandVal
FROM ActiveOrders
WHERE CASE
WHEN NOT EXISTS (SELECT a.AUF_NR AS JPO,
SUM(a.RG_OFFEN * b.SUM_NETTO) AS OpenVal,
SUM(a.RG_OFFEN * b.VER_M2) AS OpenSQM,
SUM(a.RG_OFFEN) AS OpenQty
FROM liorder..LIORDER.AUF_STAT a,
liorder..LIORDER.AUF_POS b
WHERE a.AUF_NR = b.AUF_NR AND
a.AUF_POS = b.AUF_POS AND
a.RG_OFFEN != 0 AND
a.AUF_NR = @.JPO
GROUP BY a.AUF_NR) THEN (DELETE FROM ActiveOrders WHERE JPO = @.JPO)
WHEN EXISTS (SELECT a.AUF_NR AS JPO,
@.OpenVal = SUM(a.RG_OFFEN * b.SUM_NETTO) AS OpenVal,
@.OpenSQM = SUM(a.RG_OFFEN * b.VER_M2) AS OpenSQM,
@.OpenQty = SUM(a.RG_OFFEN) AS OpenQty
FROM liorder..LIORDER.AUF_STAT a,
liorder..LIORDER.AUF_POS b
WHERE a.AUF_NR = b.AUF_NR AND
a.AUF_POS = b.AUF_POS AND
a.RG_OFFEN != 0 AND
a.AUF_NR = @.JPO
GROUP BY a.AUF_NR) THEN (UPDATE ActiveOrders
SET OutStandVal = @.OpenVal,
QtyDlv = @.OpenQty,
SQM = @.OpenQty
WHERE JPO = @.JPO)
END
...Now this is just experimental. I've tried running it and it gave me errors. I was thingking of using cursors but I think that's not efficient enough. You might notice the "fn_ActiveOrders". It's a function I created and this is the content...

CREATE FUNCTION fn_ActiveOrders(@.JPO numeric)
RETURNS TABLE
AS RETURN
SELECT OrdStat.AUF_NR AS JPO,
SUM(OrdStat.RG_OFFEN * OrdPos.SUM_NETTO) AS OpenVal,

SUM(OrdStat.RG_OFFEN * OrdPos.VER_M2) AS OpenSQM,
SUM(OrdStat.RG_OFFEN) AS QtyDlv
FROM liorder..LIORDER.AUF_POS OrdPos INNER JOIN
liorder..LIORDER.AUF_STAT OrdStat ON

OrdPos.AUF_NR = OrdStat.AUF_NR AND
OrdPos.AUF_POS = OrdStat.AUF_POS
WHERE OrdStat.RG_OFFEN <> 0 AND
OrdStat.AUF_NR = @.JPO
GROUP BY OrdStat.AUF_NR
I hope that somebody could help me with this...I was thingking of using cursors but I think that's not efficient enough.
Hi

That is defo correct - you don't need cursors for this. I rewrote your middle block of code - it looks about right but I haven't tested. Please post DDL and sample data if it fails. Please test in a test environment of course :)


Delete
FROM ActiveOrders
WHERE NOT EXISTS (SELECT NULL
FROM liorder..LIORDER.AUF_STAT a
INNER JOIN liorder..LIORDER.AUF_POS b ON
a.AUF_NR = b.AUF_NR
AND a.AUF_POS = b.AUF_POS
WHERE a.RG_OFFEN <> 0
AND a.AUF_NR = ActiveOrders..JPO)
Update ActiveOrders
SET OutStandVal = SumA,
QtyDlv = SumB,
SQM = SumC
FROM (SELECT SUM(a.RG_OFFEN * b.SUM_NETTO) AS SumA,
SUM(a.RG_OFFEN * b.VER_M2) AS SumB,
SUM(a.RG_OFFEN) AS SumC,
a.AUF_NR AS JPO
FROM liorder..LIORDER.AUF_STAT a
INNER JOIN liorder..LIORDER.AUF_POS b ON
a.AUF_NR = b.AUF_NR
AND a.AUF_POS = b.AUF_POS
WHERE a.RG_OFFEN <> 0
Group BY
a.AUF_NR) AS c
INNER JOIN ActiveOrders ON
ActiveOrders.JPO = c.JPO
Group BY
c.JPO


HTH|||This what I understood from ur VB code.I havent tested with data,so no guaranty from my side :).Test it before applying to production server.

CREATE PROC Update_ActiveOrders_sp
as
declare @.err int
set nocount on
begin tran
-- creating temp table inserting record--
SELECT OrdStat.AUF_NR AS JPO,
SUM(OrdStat.RG_OFFEN * OrdPos.SUM_NETTO) AS OpenVal,
SUM(OrdStat.RG_OFFEN * OrdPos.VER_M2) AS OpenSQM,
SUM(OrdStat.RG_OFFEN) AS QtyDlv
INTO #TEMP
FROM
liorder..LIORDER.AUF_POS OrdPos INNER JOIN
liorder..LIORDER.AUF_STAT OrdStat ON
OrdPos.AUF_NR = OrdStat.AUF_NR AND
OrdPos.AUF_POS = OrdStat.AUF_POS INNER JOIN
ActiveOrders ON
ActiveOrders.JPO=OrdStat.AUF_NR
WHERE
OrdStat.RG_OFFEN <> 0 AND
ActiveOrders.JPO < '5000000'
GROUP BY
OrdStat.AUF_NR

--update ActiveOrders which exists in #TEMP table--
UPDATE ActiveOrders
SET OutStandVal = tm.OpenVal,
SQM =tm.OpenSQM ,
QtyDlv =tm.QtyDlv
FROM
#TEMP AS tm
WHERE
JPO =tm.JPO

set @.err=@.@.error
if(@.err <> 0) goto quitWithError

-- delete records not exists in #TEMP table and ActiveOrders.JPO<'5000000'--
DELETE ActiveOrders
where not exists (
select
null
from #TEMP
where ActiveOrders.JPO=#TEMP.JPO
)
and ActiveOrders.JPO<'5000000'
set @.err=@.@.error
if(@.err <> 0) goto quitWithError
-- drop #TEMP table
DROP TABLE #TEMP
goto EndSave
quitWithError:
if (@.@.trancount >0) rollback
return @.err
EndSave:
if (@.@.trancount >0) commit tran
return 0|||Hi

That is defo correct - you don't need cursors for this. I rewrote your middle block of code - it looks about right but I haven't tested. Please post DDL and sample data if it fails. Please test in a test environment of course :)


Delete
FROM ActiveOrders
WHERE NOT EXISTS (SELECT NULL
FROM liorder..LIORDER.AUF_STAT a
INNER JOIN liorder..LIORDER.AUF_POS b ON
a.AUF_NR = b.AUF_NR
AND a.AUF_POS = b.AUF_POS
WHERE a.RG_OFFEN <> 0
AND a.AUF_NR = ActiveOrders..JPO)
Update ActiveOrders
SET OutStandVal = SumA,
QtyDlv = SumB,
SQM = SumC
FROM (SELECT SUM(a.RG_OFFEN * b.SUM_NETTO) AS SumA,
SUM(a.RG_OFFEN * b.VER_M2) AS SumB,
SUM(a.RG_OFFEN) AS SumC,
a.AUF_NR AS JPO
FROM liorder..LIORDER.AUF_STAT a
INNER JOIN liorder..LIORDER.AUF_POS b ON
a.AUF_NR = b.AUF_NR
AND a.AUF_POS = b.AUF_POS
WHERE a.RG_OFFEN <> 0
Group BY
a.AUF_NR) AS c
INNER JOIN ActiveOrders ON
ActiveOrders.JPO = c.JPO
Group BY
c.JPO


HTH

Pootle,u missed one condition here , "ActiveOrders.JPO<'5000000'"|||Pootle,u missed one condition here , "ActiveOrders.JPO<'5000000'"You are right :o I think you took a bit more time about it - I didn't bother translating the vb solution - I just reworked his T-SQL stab at it. The 5000000 didn't make a showing there (in my defence :D )|||Thanks guys! Both your solutions worked!!! Now I've removed the module from my VB and all updates now work via SQL Server Agent. By the way, since we've talked about cursors, is there a best way to use it? I mean, I've only tried it twice and it takes shitload of time before it finishes execution. I'm sorry though this is off topic, but I just wanted to learn more about T-SQL as it helps me a lot in VB programming. And the stuff that I've learnt so far I got from forums as well.|||Use cursor when there is no other option.Most of time u can solve the problem without cursors.|||Cursors are useful in about two situations -
1) looping through system tables to perform commands on, for example, each table in the database. There are variations on this theme.
2) Camparing values in a column to values from different rows in the same column.

it takes shitload of time before it finishes execution
terribly perceptive - most cursor devotees don't appear to notice this rather important point.

How to do search on two or three data tables at once!

Hi
Looking at the vb.net code below how can I modify the code so that when using SQL Servers
Full Text Search it will search two or three data tables at once Jobs Jobs2 and jobs3 all
tables are in the same database aspnetjobs and share the same job_id column. I can perform a search on one table but not two. Please look
at the vb.net code below and help if you can.

thanks!

sub button_click(s as object, e as eventargs)

Dim conaspnetjobs as sqlconnection
Dim strsearch as string
Dim cmdsearch as sqlcommand
Dim dtrsearch as sqldatareader

conaspnetjobs = new sqlconnection ( "server=mark\mark;uid=sa;pwd=clr_fcl;database=aspnetjobs" )

strsearch ="select job_fulldesc from Jobs, jobs2 where freetext( job_fulldesc, @.searchphrase )"
cmdsearch = new sqlcommand( strsearch, conaspnetjobs )
cmdsearch.parameters.add( "@.searchphrase", txtsearchphrase.text )

conaspnetjobs.open()
dtrsearch = cmdsearch.executereader()

while dtrsearch.read
lblresults.text &= "<li><br>" & dtrsearch( "job_fulldesc" )
end while

conaspnetjobs.close
end subHi,

Can't you simply join the other job tables in you query?

select job_fulldesc from Jobs a join jobs2 b on a.job_id = b.job_id where freetext( job_fulldesc, @.searchphrase )"

:-D

JB|||I think you need a UNION ALL. This will search each of the tables and combine the results into one resultset. I added a "source" column so you'd know which table the match come from; you may not need this.

"select job_fulldesc from Jobs, source="Jobs" where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc from Jobs2, source="Jobs2" where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc from Jobs3, source="Jobs3" where freetext( job_fulldesc, @.searchphrase )"

Terri|||Hi
I tryed the UNION ALL as above and recieved error message "UNION" is not declared!

please help!|||Something ran amok with the code I suggested. No wonder it didn't work. This is what I meant:

"select job_fulldesc, source='Jobs' from Jobs where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc, source='Jobs2' from Jobs2 where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc, source='Jobs3' from Jobs3 where freetext( job_fulldesc, @.searchphrase )"

I tried this out by creating a duplicate of Products called Products2 on Northwind, and then creating a full text index on the ProductName field. Then I ran this successfully:

DECLARE @.searchphrase varchar(20)
SET @.searchphrase = 'Ale'
SELECT productname, source='Jobs' FROM products WHERE FREETEXT(productname, @.searchphrase )
UNION ALL
SELECT productname, source='Jobs2' FROM products2 WHERE FREETEXT(productname, @.searchphrase )
UNION ALL
SELECT productname, source='Jobs3' FROM products WHERE FREETEXT(productname, @.searchphrase )

|||I get this error message "statement is not valid inside a method"

Declare @.sreachphrase varchar(20 )

Please Help!|||That bottom block of code I posted was strictly for Query Analyzer use as proof of concept, not to copy and paste into your ASP.NET page.

This block of code is what should work for you:

strsearch ="select job_fulldesc, source='Jobs' from Jobs where freetext( job_fulldesc, @.searchphrase ) UNION ALL select job_fulldesc, source='Jobs2' from Jobs2 where freetext( job_fulldesc, @.searchphrase ) UNION ALL select job_fulldesc, source='Jobs3' from Jobs3 where freetext( job_fulldesc, @.searchphrase )"

Terri