April 30, 2019 at 8:59 am
Hi,
I am building a report in SSRS that returns a field in a table that is encrypted on the SQL server itself. When the report is run the field just returns '#Error'.
Is there a way for the encrypted field to be displayed?
Thanks 🙂
April 30, 2019 at 9:17 am
You need to unencrypt the value before it gets to the report. If you don't know how to do that, it would suggest you don't have permission to decrypt that value; and therefore shouldn't be trying to. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 30, 2019 at 9:24 am
If you don’t know how to do that, it would suggest you don’t have permission to decrypt that value; and therefore shouldn’t be trying to.
Or maybe I don't know how to do it because I have never done it before? I am an IT administrator so I should be able to do it if you can provide some guidance please.
April 30, 2019 at 9:32 am
If you don’t know how to do that, it would suggest you don’t have permission to decrypt that value; and therefore shouldn’t be trying to.
Or maybe I don't know how to do it because I have never done it before? I am an IT administrator so I should be able to do it if you can provide some guidance please.
Unfortunately it completely depends on how the column itself was encrypted. Was it encrypted before it was inserted, or did SQL Server do it? How was it encrypted, with a key, password? Was an algorithm used, and if so which one? We'd need to know the method first, and we can tell you how to reverse it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 30, 2019 at 9:36 am
Ah right I think wires are being crossed slightly. I want the field to remain encrypted on the SQL server as it contains users passwords. I was just wondering if I can display them unencrypted in an SSRS report which judging by your response I can't do?
April 30, 2019 at 9:40 am
Ah right I think wires are being crossed slightly. I want the field to remain encrypted on the SQL server as it contains users passwords. I was just wondering if I can display them unencrypted in an SSRS report which judging by your response I can't do?
Passwords in a database should be salted and hashed; as a result there is no way to unencrypt them. Hashing is a one way process.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 30, 2019 at 9:49 am
You can unencrypt a value in a SELECT statement, yes, however, then my previous statement amount what we need to know applies. Considering these are passwords, however, then there should be no way you can decrypt (unhash) them, as I mentioned in the reply above.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 30, 2019 at 10:07 am
Well we have stored these passwords in a custom table created by our front-end application, the encryption is also handled by our front-end application so I don't really know specifically exactly how it's encrypted (the database doesn't know the values stored are passwords, it could be anything so they may or may not be hashed).
The company who make our application won't tell us how the encryption works because it sort of defeats the purpose!
I'm just gonna assume there isn't a way of achieving what I want to achieve 🙁
April 30, 2019 at 10:16 am
I'm just gonna assume there isn't a way of achieving what I want to achieve 🙁
As you should. No one should be able to decrypt a password that has been stored in a database. Even you.
Honestly, even if you had told me the method, I would have not given you an answer on "how" to decrypt the values. What you are asking to do is breach your user's data; and I have no interest in participating with such things.
Really, this goes back to what I said initially:
If you don't know how to do that, it would suggest you don't have permission to decrypt that value; and therefore shouldn't be trying to. 🙂
No one in a company should have permission to decrypt a User's password. Not the database administrator, not the IT administrator/Manager, not even the CEO of the company; and that includes you. You should not even be entertaining trying to decrypt the values, especially for a report on SSRS which will likely be accessible by more people than just yourself. That raises all kinds of red flags.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 30, 2019 at 10:54 am
We already know the passwords I'm the one that creates them! All we want is a secure place to store them (among other details) that's why we have created a custom encrypted table as before we used to store AD passwords in a password protected spreadsheet which isn't secure at all really.
April 30, 2019 at 11:00 am
We already know the passwords I'm the one that creates them! All we want is a secure place to store them (among other details) that's why we have created a custom encrypted table as before we used to store AD passwords in a password protected spreadsheet which isn't secure at all really.
I'd suggest investing in a password storage system then (we personally use pwdmanager). But still, you shouldn't be retaining the password you set for a user; that's still a security issue. If you know the the passwords for everyone, what's stopping you (or someone else who has access to them) from using getting a user's password and performing malicious actions as that user.
Considering you're using AD, then really the user should be given a temporary password when they first create their account which expires as soon as they log in, meaning they have to change it. Then you, nor anyone else, has access to their account. if they then forget their password, they would need to follow your internal process for having it reset.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 30, 2019 at 1:53 pm
To follow up on what Thom said, it is against security best practices to have passwords openly available in any environment, even a report. In fact, in many countries (such as the European Union), it's downright illegal to do what you are suggesting you need to do.
But allow me to point out a business logic issue... Your company created a custom encrypted table to store the passwords in because the password-protected spreadsheet wasn't secure enough. What is the business reason for undoing that security measure to expose those passwords in clear text? Doesn't that completely obliterate the reasoning for the custom-encrypted table?
Who's leading the push for the encryption? Who's leading the push for the report? Is it the same person or different people?
April 30, 2019 at 2:24 pm
In fact, in many countries (such as the European Union), it’s downright illegal to do what you are suggesting you need to do.
No it's not, not even in the slightest.
Who’s leading the push for the encryption? Who’s leading the push for the report? Is it the same person or different people?
That's none of your business.
What is the business reason for undoing that security measure to expose those passwords in clear text?
We can already see the passwords in clear text via our front-end application, they are just encrypted in the back-end. We have very solid permissions and privileges in our front-end which means only members of IT (of which there are only 3 of us) can see the table. The only reason I am floating the idea of having them in a report is because our front-end does not allow us to search or filter the info whereas I would be able to do that in an SSRS report. Again, I would make sure only members of IT can see the report.
I only asked the question on here to see if it could technically be done, I did not ask for your opinion on the inner workings of my company.
April 30, 2019 at 2:27 pm
I'm sorry you see my response as insulting. It was not intended that way. I was trying to assist you in understanding what a landmine you are sitting on and walk you through the problem.
Since you seem to have no desire to save yourself and your company from potential legal issues, lawsuits and government crackdowns, I will refrain from trying to assist you further.
April 30, 2019 at 3:54 pm
I only asked the question on here to see if it could technically be done, I did not ask for your opinion on the inner workings of my company.
And it sounds like you already got the answer to that, if the passwords are encrypted by the FE application and they won't tell you the encryption algorithm no you won't be able to decrypt them in SQL Server.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply