May 17, 2009 at 7:43 pm
Hi,
Can somebody explain if application role password is sent as it is to database server. Our .net application stores the password (encrypted) & activates the application role by calling sp_setapprole sysproc. We're using sql server 2005 database.
For example "ABC" application role's password "XYZ" will be sent as "XYZ" over the network?
Our compliance dept wants us to use SSL and that could impact the application performance significantly.
Thanks in advance.
May 17, 2009 at 8:00 pm
You can run a trace with Profiler to check this, but I believe this is sent over the network.
You can put this in a stored procedure, which is arguably safer, and require that only certain users have rights to this procedure.
However your data will go clear text without SSL as well. That might not work for your compliance group either.
May 17, 2009 at 8:13 pm
Can't see the password in profiler.
I also think the password is sent in clear text but struggling to find an article for sql 2005.
If I'm not wrong sp_setapprole can not be executed from inside a stored proc.
MSDN documentation (http://msdn.microsoft.com/en-us/library/bb669062.aspx) says "Beginning with SQL Server 2005, the parameter password is stored as a one-way hash.". What does it mean?
May 17, 2009 at 8:27 pm
That may be a profiler thing. I didn't think this was counted as a password since it's a stored procedure parameter.
To be sure I'd ask your network people to set up a network trace and grab the TDS packets.
September 2, 2009 at 3:08 am
Hopping in way to late for this thread, but as I was confronted with application roles nolonger working in SQL2005 for a restored SQL2000 database....
BOL states:
The encrypt option is not supported on connections that are using SqlClient.
Important:
The ODBC encrypt function does not provide encryption. You should not rely on this function to protect passwords that are transmitted over a network. If this information will be transmitted across a network, use SSL or IPSec.
The issue I have been looking for was this:
In SQL2000 for case insensitive databases, the application role password is challenged case insensitive !
In SQL2005 all password checking is case sensitive !
Some of my devs succeeded in not using copy/paste, but providing uppercase passwords.
So for some applications, this fails when trying to switch to the application role.
😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 2, 2009 at 7:14 am
That's interesting, Johan. Never knew that, but it's a handy piece of advice to keep in mind.
Does this still apply in a CI db?
September 2, 2009 at 7:30 am
Are you refering to SQL in the Cloud ?
I don't know :unsure:
We're not into cloud surfing .... at least not yet 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 2, 2009 at 7:33 am
sorry, CI = Case insensitive, as opposed to CS, case sensitive.
Acronyms used in the code page setup at one point.
September 2, 2009 at 7:57 am
:blink: :blink: Where's my donckey 😀
I've been reading a bit on the cloud topic ..... already forgot the obvious :blush:
At my plant, we only have CI databases.
I have promoted case insensitive to be the default, so we only use case sensitive databases if some obscure software really really really must have it :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 2, 2009 at 8:01 am
:blink: :blink: Where's my donckey 😀
I've been reading a bit on the cloud topic ..... already forgot the obvious :blush:
At my plant, we only have CI databases.
I have promoted case insensitive to be the default, so we only use case sensitive databases if some obscure software really really really must have it :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply