Viewing an encrypted field in SSRS report

  • mharbuz

    Mr or Mrs. 500

    Points: 506

    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 🙂

  • Thom A

    SSC Guru

    Points: 98219

    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.

  • mharbuz

    Mr or Mrs. 500

    Points: 506

    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.

  • Thom A

    SSC Guru

    Points: 98219

    mharbuz wrote:

    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.

  • mharbuz

    Mr or Mrs. 500

    Points: 506

    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?

  • Thom A

    SSC Guru

    Points: 98219

    mharbuz wrote:

    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.

  • Thom A

    SSC Guru

    Points: 98219

    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.

  • mharbuz

    Mr or Mrs. 500

    Points: 506

    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 🙁

     

     

  • Thom A

    SSC Guru

    Points: 98219

    mharbuz wrote:

    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:

    Thom A wrote:

    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.

  • mharbuz

    Mr or Mrs. 500

    Points: 506

    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.

  • Thom A

    SSC Guru

    Points: 98219

    mharbuz wrote:

    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.

  • Brandie Tarvin

    SSC Guru

    Points: 172517

    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?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • mharbuz

    Mr or Mrs. 500

    Points: 506

    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.

     

     

     

     

  • Brandie Tarvin

    SSC Guru

    Points: 172517

    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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • ZZartin

    SSC-Dedicated

    Points: 30314

    mharbuz wrote:

    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 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply