Hi ,
I wanto know whether it is possible to create a hash id f 8bytes from 31 character string using any T-ql functions.
If it is possible, I need to know whether the function works fine in Sql 2000?
Please help me out this with some example. That would be of great help.
Thanks
Anoop
Ok, here is what i'm trying to do and its driving me nuts.
ok,
1) I have a proc that runs and needs to validate the user prior to running - this proc is called from an hand held device
2) the id and password are being passed as "clear text" but the password is stored in the database table hashed.
Is there anything on the db side that can get the hash value from the password column of the aspnet_membership table and compare it to the password being passed in to this proc? I have suggested several options to the handheld developer but nothing. This has to be done on the database side.
so,
username and password are passed to proc from handheld.
proc needs to validate ther user in the aspnet_membership table
if user id and password are valid execute the stored procedure
is this possible? if so can ANYONE point me to some examples of this being done?
We have a mobile device project, which has a database file (Database.sdf) as part of the project.
I am trying to connect to it to insert a record. The syntax I'm using is:
string connection = @"data source=Database.sdf";
SqlCeConnection conn=new SqlCeConnection(connection);
conn.Open();
I have tried various connection strings such as
"data source=MyDocumentsDatabase.sdf";
and
"data source=C:MyProjectsThisParticularProjectDatabase.sdf"; << the connection string in teh server explorer
and
"data source=MyAssemblyName.Database.sdf";
and
"data source=MyAssemblyNameDatabase.sdf";
I've also tried getting the path the the executing assembly and adding that in, but still no luck.
but they all throw the error:
The database file cannot be found. Check the path to the database. [ File name = Database.sdf ]
I have searched the internet and found no explanation about how the path to the Database is made up (ie is it the path on *my* computer, or the virtual mobile device?) and how I can actually get the path the the database without hardcoding it.
Can anyone help please??
Thanks!!
I have a table with a startdatetime and an enddatetime column such as:StartDateTime EndDateTime what I want to see returnedis:01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/20041:30PM"01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/200410:30AM"Maybe someone has accomplished this aready in a stored procedure andhas an example of how to do it?lq
View 2 Replies View RelatedI am ultra new to this so thanks in advance for any help.
I was trying to create a connection to a database that I created in SQL Express. I am essentially trying to submit three attributes to the existing database from a table that consists of three textboxes and a submit button. I would like all of the code to be in the head of the page (because that is the standard here) so I wanted to know what the connection string should be in Visual Basic 2005 Express to establish a connection on the same machine. I'm not sure about the connection string, but I am also not sure about a lot of the code. Also, the Using clauses seem to give me an error (where should it go?). This is what I have in the head of the page (visual C# by the way). Also, I got this from http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx :
Using System.Data;Using System.Data.SqlClient;protected void Button1_Click(object sender, EventArgs e){SqlConnection conn = new SqlConnection("Data Source=(local);Database=CustomersDB;Trusted Connection=true");conn.Open();string insertstring = @" insert into Catagories (CustomerID, CustomerName, CustomerEmail) values (" + textbox1.value + ", " + textbox2.value + " + ", textbox3.value");SqlCommand cmd = new SqlCommand(insertstring, conn);Sql.ExecuteNonQuery();conn.close();}
If there is absolutely any insite into the problems with my code, I really appreciate it.
I have a need to create a delimited string so that I can use this to create a data driven subscription on SSRS.
In the below code, I need to create a delimited string using the branch number, grouped by the email address
USE tempdb
GO
IF OBJECT_ID('tempdb..#emails') IS NOT NULL
BEGIN
DROP TABLE #emails
[code]....
I have a need to create a table in a sql server database from C# code. The kicker is that the user must be able to specify the table and field names via the UI. I can do a bit of sanity checking but as long as they enter something reasonable I need to accept it. Normaly I always ADO parameters to sanitise any user parameters but they can't be applied to table and field names, only values. As far as I'm aware that leaves me needing to concatenate strings and that's something I usually avoid like the plague due to risk of SQL injection.
My actual question : Assuming string concatenation is my only way forward, how can I sanitise the values that would go into the table name and fieldname bits of a CREATE TABLE statement to ensure that injection can't occur? I've been pondering it and I think I just need to check for semi-colons. Without a semi-colon I don't think a user could inject an extra statement could they?
Hi,
I need to take the Social Security number and hash it into a unique identifier because I can't display Social Security numbers on a report.
Does Reporting Services have any encrypting/hashing routines that I can use?
Any suggestions?
Thanks.
Hello,
I would like use hash function from C# in my SQL procedure. But I don't know how.
Martina
It seems like there is no built in procedure for making a password hash inSQL2000. Am I wrong.Do I have to make it from scratch myself or is there samples out there?/Jens Ulrik
View 3 Replies View RelatedHi!
I want to know wether SQL Server 2005 (that I use) has a built in support for creating MD5 hashes and if it has then can someone give me an example how to use it.
I need to create hash of image field and then compare it to another one that has been sent from application. The hash from my app is sent as string so it would be great if the hash result would be returned as 32 character string
The Software is wrintten in visual basic .net 2005 and it has a built in MD5 hash function so I have taken care of that part.
P.S. If SQL Server 2005 doesn support MD5 maybe someone know other hash alghoritms that Server does support (maybe one that also is supperoted by VB.NET 2005).
Many articles on sql server security make reference to Hash functions. Do you know of a simple example of a hash function that I could show to others?
For example, would taking the first eight bytes of the mathematical "sin" of a number be a good function? I don't know. Or is a hash "function" actually an involved algorithm, so the "simple" formula I was looking for really doesn't exist.
TIA,
Barkingdog
Do we have a hash structure in t-sql ? I want that a stored procedure accept a hash table as input (which contains 'column names => new_value' as keys and values). A small piece of code demonstrating the use can be useful.
Hi, I've been through Books Online, Technet, MSN and the archives on this site trying to get a difinite answer on this. Everything except Technet indicates a maximum configurable value of 265003. However in Technet, it says that if you are running the enterprise edition with more than 2GB of memory that you can configure this to a much higher number (their example is 700000). Using the calculation involving memory, and based on several dozen performance tests, we would like to set our value to around 666000 (using the prime number of course). However we cannot get SQL to accept any value over the default maximum.
Is Technet wrong? Are we missing something? I cant find any reference other than in Technet on being able to use the higher number.
Thanks in advance,
Chris
Hiya,
I am wondering, in theory, how I would use a typical one-way hash function to protect customer password on my website server (which is a small e-commerce server)?
TRhanks MSDN, as always!
I have implemented a script to perform a MD5 hash on each row processed by the SSIS package so that it can be compared with a stored value to see if there has been a change in the record. This package processes over 1 million rows. In 12 of these rows I get a hash value that is different than the stored value despite the fact that the rows "look" identical. Curious about this, I used the both the CheckSum and Binary_Checksum feature from t-sql to check the rows and they both show the identical checksum value. I have exported the rows into text and did a compare and the records are identical. I assume there must be some hidden characters that is causing the hash to be different, has anyone else run into this issue? Any help is much appreciated.
In my example I join two tables DimCustomer and FactInterSales from AdventureWorksDW database
In T-SQL it's simply query
Code Snippet
select a.CustomerKey,
a.FirstName,
b.SalesOrderNumber
from AdventureWorksDw.dbo.DimCustomer a,
AdventureWorksDw.dbo.FactInternetSales b
where a.CustomerKey=b.CustomerKey
In SSIS it's simple task too
€˜As input two queries stored in variable ADO.Recordset
Code Snippet
Public Sub Main()
Dim SrcAd As New OleDbDataAdapter
Dim SrcA As New Data.DataTable("DimCustomer")
Dim SrcB As New Data.DataTable("FactInterSale")
Dim DstC As New Data.DataTable("Output")
Dim TabA As New Hashtable()
SrcAd.Fill(SrcB, Dts.Variables("varFactInternetSales").Value)
SrcAd.Fill(SrcA, Dts.Variables("varDimCustomer").Value)
€˜ create destination product
Dim col01 As DataColumn = New DataColumn("CustomerKey")
col01.DataType = System.Type.GetType("System.Int32")
DstC.Columns.Add(col01)
Dim col02 As DataColumn = New DataColumn("FirstName")
col02.DataType = System.Type.GetType("System.String")
DstC.Columns.Add(col02)
Dim col03 As DataColumn = New DataColumn("SalesOrderNumber")
col03.DataType = System.Type.GetType("System.String")
DstC.Columns.Add(col03)
€˜ populate hash table based on PrimaryKey
For Each row As DataRow In SrcA.Rows
TabA.Add(row.Item("CustomerKey").GetHashCode(), row)
Next (row)
Dim myNewRow As DataRow
Dim tmpRow As DataRow
€˜ make hash join
For Each row As DataRow In SrcB.Rows
tmpRow = CType(TabA(row.Item("CustomerKey").GetHashCode()), DataRow)
myNewRow = DstC.NewRow()
myNewRow("CustomerKey") = tmpRow.Item("CustomerKey")
myNewRow("FirstName") = tmpRow.Item("FirstName")
myNewRow("SalesOrderNumber") = row.Item("SalesOrderNumber")
DstC.Rows.Add(myNewRow)
Next (row)
€˜ write DataTable in SSIS variable for other processing
Dts.Variables("varOutput").Value = DstC
Dts.TaskResult = Dts.Results.Success
End Sub
Here what I want to accomplish, I have table that has 15 column one of the columns is ClientID that is about max 45char. I want to be able to hash on the column and return the hash value in a new column call ClientID_Hash. Is this possible?
View 2 Replies View RelatedHi there,I have created a hash table. After using it, somehow the primary keyconstraint of this hash table still exist in database. Which causeerror.When I delete this constraint with Alter table Drop con....It gives no table exist error.Can anybody give any idea.Thanks in Adv.,T.S.Negi
View 2 Replies View RelatedI have two queries that seem to be the same, but perform very differently. The first query runs very fast (7000+ records returned in <1 sec.). The execution plan shows that it uses a nested loop with index seeks on both tables.
select *
from t_loadbasic
where ld_nbr in (select ld_nbr from t_tripcombined where comp_date between '11/1/07' and '11/05/07')
The second query is almost the same, save the fact that it uses date variables instead of hard dates. The execution plan shows that it uses a hash match instead of a nested loop with an index scan on the main table (t_loadbasic). This query takes about 12 seconds to run.
declare @startdate datetime
,@enddate datetime
set @startdate = '11/1/07'
set @enddate = '11/5/07'
select *
from t_loadbasic
where ld_nbr in (select ld_nbr from t_tripcombined where comp_date between @startdate and @enddate)
I'm trying to figure out why the database executes these two statements so differently. BTW, I've tried switching the order of the tables. I've tried using joins instead of a subquery. The execution plan seems completely dependant on the use of variables. I can attach the execution plans if necessary.
I apologize if this is too simple a question, but I couldn't find an answer on any forums, web searches or BOL. Thanks in adavance.
Could Any body Please give me one example of each of three types of joins that is
Merge Join
Hash Join
Nested join
Hi,
We are using binary_checksum in some of instead of update trigger. The problem came into the knowledge when update falied without raising any error. We came to know after research that checksum returns same number for two different inputs and thats why update failed.
We are using following type of inside the trigger.
UPDATE [dbo].[Hospital]
SET
[HospitalID]= I.[HospitalID],
[Name]= I.[Name],
[HospitalNumber]= I.[HospitalNumber],
[ServerName] = I.[ServerName],
[IsAuthorized]= I.[IsAuthorized],
[IsAlertEnabled]= I.[IsAlertEnabled],
[AlertStartDate]= I.[AlertStartDate],
[AlertEndDate]= I.[AlertEndDate],
[IsTraining]= I.[IsTraining],
[TestMessageInterval]= I.[TestMessageInterval],
[DelayAlertTime]= I.[DelayAlertTime],
[IsDelayMessageAlert]= I.[IsDelayMessageAlert],
[IsTestMessageAlert]= I.[IsTestMessageAlert],
[IsUnAuthorizedMessageAlert]= I.[IsUnAuthorizedMessageAlert],
[IsWANDownAlert]= I.[IsWANDownAlert],
[IsWANUpAlert]= I.[IsWANUpAlert],
[CreateUserID]= Hospital.[CreateUserID],
[CreateWorkstationID]= Hospital.[CreateWorkstationID],
[CreateDate]= Hospital.[CreateDate] ,
/* record created date is never updated */
[ChangeUserID]= suser_name(),
[ChangeWorkstationID]= host_name(),
[ChangeDate]= getdate() ,
/* Updating the record modified field to now */
[CTSServerID]= I.[CTSServerID]
FROM inserted i
WHERE
i.[HospitalID]= Hospital.[HospitalID]
AND binary_checksum(
Hospital.[HospitalID],
Hospital.[Name],
Hospital.[HospitalNumber],
Hospital.[ServerName],
Hospital.[IsAuthorized],
Hospital.[IsAlertEnabled],
Hospital.[AlertStartDate],
Hospital.[AlertEndDate],
Hospital.[IsTraining],
Hospital.[TestMessageInterval],
Hospital.[DelayAlertTime],
Hospital.[IsDelayMessageAlert],
Hospital.[IsTestMessageAlert],
Hospital.[IsUnAuthorizedMessageAlert],
Hospital.[IsWANDownAlert],
Hospital.[IsWANUpAlert]) !=
binary_checksum(
I.[HospitalID],
I.[Name],
I.[HospitalNumber],
I.[ServerName],
I.[IsAuthorized],
I.[IsAlertEnabled],
I.[AlertStartDate],
I.[AlertEndDate],
I.[IsTraining],
I.[TestMessageInterval],
I.[DelayAlertTime],
I.[IsDelayMessageAlert],
I.[IsTestMessageAlert],
I.[IsUnAuthorizedMessageAlert],
I.[IsWANDownAlert],
I.[IsWANUpAlert]) ;
Here is the checksum example which produces same results for two different input.
DECLARE @V1 VARCHAR(10)
DECLARE @V2 VARCHAR(10)
SELECT @V1 = NULL, @V2=NULL
SELECT binary_checksum('KKK','San Jose','1418','1418SVR ',0,1,@V1,@V2,0,30,180,1,0,1,1,1),
binary_checksum('KKK','San Jose','1418','1418SVR ',1,1,@V1,@V2,0,30,180,1,1,1,1,1)
Lookat the two binary_checksum above, they are different and should not match, but they both return same value.
Can someone please provide some info on these.
Hi Guys,
I'm importing logs from multiple servers from CSV format into a table. Inside the loop the 1st thing I do is insert the FileName into a table to ensure that I never process the same file twice.
The problem is that multiple servers produce the same filename, so if server A produces Log20061110.csv - when I import the log from server B ( also called Log20061110.csv ) into the table, it of course runs into a problem.
If there was some way for me to get an MD5 Hash of the file as a variable that would be solve my problem.
Does anyone know how I could accomplish that?
Thanks
Warrick
I would like to perform an audit of weak passwords, which is well documented for sql users. Using the same methodology, I should be able to audit weak passwords for contained users. To accomplish this, I must be able to find the location of the password hashes for the contained users.
I have looked at sys.syslogins and sys.sql_logins, which both have the password hash for server level users, but not contained users. I was able to find sys.sysusers, which does contain contained users, but no password hash.
What is the location of the password hashes for contained users?
When I have a record that I copy and paste (Access 2010 datasheet view) into a new record with one column having a unique constraint , I display message "Cannot insert duplicate code" then if not all values in the record are inserted , I get a #deleted in all columns Refreshing , I find that the id is incremented by 2 instead of one.
That is if the previous record has ID 20 , the #deleted record has ID 22 .
avoid the #deleted which happens if not all columns are filled.
How to find hash algorithm and salt value for sql logins in sql server 2005.
View 7 Replies View RelatedI have following hash table in C#.
Rank Slug
1 ABC
2 XYZ
3 PQR
I want to pass hash table or data table to the store procedure and use the looping inside the store procedure.
Hopefully this makes sense, not sure what to even begin researching...
I'm trying to optimize all facets of this process, as it will take over the resources on my server if not done efficiently.
I have CSV files containing INTs that I need to upsert (match to an existing/earlier imported array or create a new record set) millions of times a day. To be clear, this data is a small subset of the actual import, this arrays contents are not the main data of the process, and the value of the entire array is meant to be related to higher level tables.
The contents of the CSV array are 99.9+% repeating, meaning they will very often share the exact same contents as a a previously imported array. A rough guess is there are 20k combinations existing, and less than 1k new per month, and will range from 6 cols x 15 rows to 6 cols x 50 rows.
So current plan is to use a MD5 hash during the (not SQL related) export process to identify the contents of this CSV file, and export only the md5 (32 digit hex) as a lookup to identify the contents. If the SQL import process finds a new (unknown) MD5 it will request the actual contents, otherwise it will simply use the MD5 as a key/id/code for the actual array contents that are already stored.
There's probably a certain terminology I'm not familiar with for this type of thing.. I've never heard of something like this. I realize collision is a threat, but I'm unsure how much I should be worried about it with this type of data (similar size/contents, but a relatively small amount of possibilities). I think up to even 0.1% collision would be acceptable which is probably way more than enough.
Does this sound like a bad idea to anyone? Are there certain hash functions I should use for this type of thing? Anyone have suggestions of where to look next?
Thanks!
We have one single hash (#) table, in which we insert data processingpriority wise (after calculating priority).for. e.g.Company Product Priority Prod. QtyProd_Plan_DateC1 P11100C1 P22 50C1 P33 30C2 P11200C2 P42 40C2 P53 10There is a problem when accessing data for usage priority wise.Problem is as follows:We want to plan production date as per group (company) sorted order andpriority wise.==>With less data, it works fine.==>But when there are more records for e.g. 100000 or more , it changesthe logical order of dataSo plan date calculation gets effected.==Although I have solved this problem with putting identity column andchecking in where condition.But, I want to know why this problem is coming.If anybody have come across this similar problem, please let me knowthe reason and your solution.IS IT SQL SERVER PROBLEM?Thanks & Regards,T.S.Negi
View 5 Replies View RelatedWhere is located the hash password for the contained database users?I have a script that prints all creating statement so that a Dev environment security can be reapply after a prod data refresh but I can't find the table containing the hash password when the user is "with login" for contained database.
View 4 Replies View Related I was told that the authentication is a process that concerns about security. So the authentication process should be hidden from the client as possible and the client should receive a flag value (1= password is correct and authenticated, -1= incorrect password) and/or user name of the authenticated user. So it should be a SQL operation that should occur in the back end. So the process in detail should be, client form sends user name and hashed /encrypted password to SQL, SQL authenticates and it sends 1/-1 with the user name to the client as the return value and the output.
But when i do research that was not the case, all the threads i have seen on web, either the app is windows or web, developers do two operations:
1. the salt value of the user is searched by user name then return the salt value along with the PasswordHash to the client form / or to the business layer
2. Then use the entered password and the returned salt value to generate a PasswordHash and then compare this passwordHash with the returned passwordHash. if match password is correct if not password is incorrect.
Is this actually how developers write code to authenticate a user in a login event? If so basically the password comparison happens at the client side(either in the form or in the business layer) then wouldn't this be huge security risk by exposing the actual salt and passwordHash to the client?
We're encrypting a column of Social Security numbers. Developer needs to execute a query -->similar<-- to:
Select <bunch of columns>
From dbo.People p
Where p.SSN = @Input_SSN_Param --I know this won't work.
If we write the where clause like
Where DecryptByKey(p.SSN) = @Input_SSN_Param
Won't every SSN in the table be decrypted? Seems like a bad idea, performance-wise.
Should I/we add a column to hold a hash of the SSN; and use the hash for comparisons?