Chat
Topic : Securing SQL Server Chat Expert : Vineet Gupta (Microsoft IT Pro Evangelist)
April
22, 2005
Connecting
to chat service...
Manik connected.
Welcome to the India Community Chat Room. The chat room will be used
for both peer-to-peer, as well as, event chats. Please abide by the
Chat Code of Conduct, located in the Help file. If you would like
to volunteer to be a chat expert, please register here: http://www.microsoft.com/india/communities/chat/register.aspx.
To view a list of our upcoming chats, please visit: http://www.microsoft.com/india/communities/chat. Chat Room Status: Open Peer-to-Peer Technical Chat
in Progress apk007: Prasanna from bangalore gaurav:
anybody from security? Chakravarthy:
see Mr. Manik has come Chakravarthy:
as a moderator Kalyan:
hi guys gaurav:
manik, will our room automaticlly change to "sql server"
after 3 PM or we have to log in again Manik
(Moderator):
Hi Everybody, we will be starting in another 3 minutes. So just hang
on there and get to know each other till then. Padam: This is really good idea Padam: I am from Hyd and working for Microsoft, Hyd gaurav:
hi from honeywell india Padam:
come on guys Kalyan:
hi,,, today what r the topic we r going cover in SQL ? Kalyan:
from Bangalore Manik (Moderator):
Hi everyone, welcome to the weekly IT Pro Chat Padam: Can everybody introduce ourselves? Manik
(Moderator):
Today we are going to cover the Security aspect within SQL server Kalyan:
ok Manik (Moderator):
We have with us Mr. Vineet Gupta who will address all your questions
regarding the same. He will also introduce us the the new features
of SQL Server Manik (Moderator):
The Title of the Chat is "Securing SQL Server" gaurav:
are we going to cover sql injection also? Manik
(Moderator):
Welcome Vineet Kalyan:
welcome vineet gaurav:
Goodafter Mr. Vineet. Chakravarthy:
welcome Vineet Manik (Moderator):
You are now active in the chat room and we have an eager group of
people wating to ask you everything about SQL Vineet
Gupta:
Thanks Manik! Hello people! Its a pleasure being here with you!! Today
we are discussing SQL Server 2005 security. Being a chat, I have no
specific agenda / structure in mind. Just go ahead and shoot your
questions and I will try and answer them to the best of my ability. Manik (Moderator):
Lets Go !!!! Vineet Gupta:
Remember to post the questions in the questions window - allows associtation
of answer and question gaurav:
sir, i have concern about default security in sql server Vineet
Gupta:
whats the concern? Manik (Moderator):
Please remember to select the "Question" Radio button in
your chat dialogs whenever you ask any question
Padam: Why should we migrate to sql 2005 from sql 2003? Chakravarthy: Hi Vineet... can you tell me about the cryptic related
issues to store the data in SQL 2005 gaurav:
like so many vulnerable standrd procedure
Padam: what kind of security features are added in sql 2005? Vineet
Gupta:
Padam, there are several reasons for migration. We discussed this
earlier in the chat on sql server architecture last week. Since this
is a security related chat, let me do the security questions first,
and if we have time, I'll come back to the generic questions in the
end. Chakravarthy:
Manik.. is there any facility for storing data in the columns? Vineet Gupta: May I request everyone to repost their questions using
the questions button. It helps me track and answer questions Chakravarthy: Manik... i couldn't see the "Question Button"
... gaurav:
sure.. sir, i have concern about default security in sql server Chakravarthy:
it seems there is a small problem with the chat window it self...
gaurav: lets use star then Manik (Moderator): I will resolve this issue, till that time use a Star Option Chakravarthy:
fine... Manik... i have asked you couple of questions... Chakravarthy:
one) Is there any mechnanism for storing data in columns with cryptic
way? gaurav: sir, what are the special security features added in sql
2005 ? Chakravarthy:
two) what are the security features that applied on data that is stored
in tables there... as other developers that are using my data Vineet Gupta:
Gaurav, I was not able to understand your concern about vulnerable
stored procedures and the security. Please give me a scenario where
you think we do not meet your security expectations. I will try and
address that. Kalyan: MS SQL how many default user login ?* gaurav:
for example, default blank sa password. why dont we force developers
to put a password to sa username whenever sql server is installed.
i have found many sql server vulnerable jeet: what are the security upgrades in 2005 as compared to
earlier version? Veeral_Pune: How to link oracle with SQL Server? gaurav: also, for the encryption function to encrypt password
is very week.. exploit code for breaking the same is avaliable publicably Chakravarthy:
they can tamper too if they can read that Vineet
Gupta: Mr. Chakravarthy, about storing encrypted data in SQL
Server, we have made a bunch of enhancements to SQL Server 2005 from
a native encryption stadnpoint. There is a set of built-in capabilities
for encryption, decryption, signing and verification. There is a full
fledges Key management infrastructure which allows storage of keys
managed by SQL Server, keys managed by end-user. All keys are always
stored encrypted. We support Symmetric Keys (RC4, RC2, DES Family,
AES Family) and Asymmetric Keys (RSA) + Certificates. There are sample
scripts for column level encryption
mohit: but all these keys are standard from of encryption anyone can
hack them if he knows all standards and some decryptiion Chakravarthy: Vineet Gupta:
Gaurav, since SQL Server 2000 SP3 onwards, we dont allow blank password
in SQL Auth. In any case, we have been recommending since ages that
people use Windows Integrated Auth. The only reason for retaining
Mixed mode is backward compatibility. In SQL Server 2005 there is
a concept of password policy where even SQL Auth can use the password
policy as defined in Windows group poilcy and enfore it mohit:
can't v make some enryption strategy that can change with time of
the day so it can be more secure as it will be random mohit: Chakravarthy:
hi ... some one tell me ... can you read my messages... i'm lost gaurav:
the problem with windows authentication is that password haseshes
are very easy to crack using time-memory techniques, recently tools
like rainbow crack etc can crack alpha numeric passwords of upto 13
characters in one minute. gaurav:
i am not blaming, just a suggesion.. can we have modular authetication
SQL Server: Hi All, Can you all see a "Submit a Question"
radio button. Just above the "Send Button". Please select
that when you are submitting the questions.. Chakravarthy:
we Can't see that button man Channel16:
yep me too SQL Server: ok , i am
trying to fix that, nevertheless please keep your chat going ... gaurav: also, what about defaul vulnerable sql procedure.. like
xp cmd shell etc ? have you removed these from sql 2005 ? or atleast
make them disabled by default Vineet
Gupta:
Mohit, decrypting data encrypted with these standard algorithms is
very very tough even if you know the algorithm inside out and backwords.
Agencies like NSA in the US do nothing else but come up with ways
to decrypt internet and cellular phone traffic which is encrypted
with these algorithms in order to determine potential terrorist attacks
and they use super computers for this purpose. So rest assured that
strong encryption with std algorithms is not being broken in a hurry jeet: would like to know f we can create trusted connections
on a remote sql server 2005 Vineet
Gupta:
Jeet, there are several security enhancements. My top of mind recall
is: Cryptography Support
Authentication Protocol Enhancements
Password Policy Enforcement
Endpoint Auth
Module Execution Context
Impersonation
Code Signing
Granular Permission Control
Catalog Security
User-Schema separation
Row level security
SQL Server Agent Security
DDL Triggers
SQLCLR Security Chakravarthy: is the chat active? jeet:
any changes to ddl triggers Kalyan:
yes Amit:
does sql server 2005 supports cursors Chakravarthy:
Vineet: Can you please tell me about, how can i store data with encrypted
way ? jeet: eg... sridhar: Chakravarthy: in other words, the data coming from Application should
only be seen as it is from the application only... not even if some
one fires a "Select " Query on the table... Manik
(Moderator): Request you all to wait for the answers before you post
your next questions, otherwise we will get lost. jeet: any changes to ddl triggers. Channel16:
Vineet, r u also covering the Security module with respect to Credentials,
Server roles etc. I got to know there are changes in SQL 2005 in comparision
to QSL 2000? (PS: Radio button cannot c the radio button to post as
a question) jeet: arent able
to see the radio button to post a question... jeet:
any changes to ddl triggers. Vineet Gupta:
Mr. chakaravorty, i answered your ques above. Basically, we have a
key management infra in Sql2k5 and there are sample scripts provided
to achieve exactly what u want Chakravarthy:
yes jeet Channel16: yep...! Sorry to disturb on that issue jeet:
what are those Mr. chakravarty Chakravarthy:
Vineet Sorry for that .. i couldn't read that... as i logged out and
logged in during that time gaurav:
for those who joined late, use the STAR button, QUESTION button is
not there. Kalyan:
Vineet, I like to ask u, how we can explorer the SQL Log file? is
there any tool? Chakravarthy: Vineet: Can i know where
i can find such scripts ? SQL Server: Hey Kalyan,
why are you so angry.. I am sure Vineet is going to address your question
too jeet:
no star button SQL Server: Jeet please
use a Star to indicate your questions gaurav: Vineet
Gupta: Gaurav, AFAIK, Time-Memory Trade-Off Techniques
use pre-calculated tables which contain every possible combination
of characters in a Windows password and a sophisticated search algorithm.
The result is quicker password cracking. However with NTLMv2 and Kerberos
they are not very effective. So our advise to folks is to switch to
NTLMv2 or Kerberos. This becomes even more difficult if people use
pass phrases which go beyond alphaneumeric. gaurav:
2nd column and 4th row rsc: hi manik. whats up Veeral_Pune: How do i make sql server talk to oracle? Vineet
Gupta:
Gaurav, xp cmd shell etc. are deprecated with sql2k5. For such needs,
we now have sqlclr. And if you have seen sqlclr, the security model
is terrific. You can simply deny at a server that certain assemblies
are ever going to be loaded. yamini:
Does SQL Server 2005 support cursors? Manik
(Moderator):
Here is the answer on Crypto Support that was answered earlier. This
is for everybody's reference again, Manik
(Moderator):
Jeet, there are several security enhancements. My top of mind recall
is: Cryptography Support
Authentication Protocol Enhancements
Password Policy Enforcement
Endpoint Auth
Module Execution Context
Impersonation
Code Signing
Granular Permission Control
Catalog Security
User-Schema separation
Row level security
SQL Server Agent Security
DDL Triggers
SQLCLR Security Vineet Gupta:
Jeet, there are several changes to the authentication model in SQL2k5.
A connection to Sql is now always encrypted - if you dont provide
a SSL certificate, we generate a 512-bit cert on on the fly. And then
Sql after validating your login, inspects the security poilcy in effect
and only then opens a connection Kalyan:
can u pls explain about SQLCLR Security? gaurav:
Sir, as many web applications are vulnerable to sql injection, do
we have some feature in sql that can detect the same, i do understabd
its very difficult task to accomplish, as sql server is used in backed,
but still do we have some technique. Vineet
Gupta: Request folks to restrict questions to security issues
only
shrinath: hi Vineet Gupta: kalyan, the idea in sqlclr is to prevent managed code
from performing operations that compromise robustness and safety (like
calling unmanaged code, thread operations, UI, ...), while still allowing
the SQL admin control over access to external system resources (files,
network etc.). This is achieved by integrating two security models:
CLR: Code Access Security
SQL: User-authentication/authorization
This is achieved using SQL Server policy: An App-domain policy level
on top of enterprise/machine/user-levels of policy. The net permissions
given to code is the intersection of all policy levels. Specifically,
SQL Server Policy level allows mapping of user/system assemblies to
corresponding permission sets. king: how to secure database in sql server Vineet
Gupta: gaurav, on SQL injection, we are considering a couple
of solutions like checking batching of statements and some other techniques.
But the best defense is to avoid query composition and go the stored
proc way. king:
why is the minimum system requirement so high for sql server 2005
Vineet Gupta:
King, you have aked a king-size question! :-D On a serious note, I
cant possibly answer it here. Have a look at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx
for a comprehensive answer gaurav: sir, what other input validation attacks like format strings
etc? some solution in foresight? Vineet
Gupta:
kalyan, the log file is an internal mechanism used by sql server to
maintain ACID operations. It is opaque and not meant to be read by
an external tool Vineet Gupta:
gaurav, as you yourself point out in the ques, the solution is input
validation. It is not hard to do - just a matter of careful coding.
Any solutions which we come up with would also affect some legitimate
cases where such strings are valid input. king: how will be cursors useful in accessing data from a database
to application gaurav: sir,
what about the update mechanism, do we have seprate update mechnaism
for updating sql server, i guess since sql server is now being used
in mission critical applications, its worth to have sepate update
client/mechanism for updating sql server and CTQ king: why cant we have more than one primary key when it is
so useful?? gaurav: must be that it should not affect functionality of sql
server Vineet Gupta: king, our min requirements for sql are dreadfully low.
Where did you get the notion that it is high? Check out http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp Channel16:
Vineet, I have Yukon Feb CTP I am unable to deploy the CLR assemblies.
What security check points should I look at? Channel16: Vineet, I have Yukon Feb CTP I am unable to deploy the
CLR assemblies. What security check points should I look at? Channel16:
It keeps on shouting unable to start CLR....etc etc.. king:
Mr.Vineet i read that sql server 2005 requires min 512 mb ram and
.net framework 1.1 is it correct ? Channel16:
frame work 2.0 is required as far as I know Manik
(Moderator):
Hi All, can we please restric the questions to "SECURITY"
only. Vineet Gupta: sql is based on clr 2.0 Manik
(Moderator):
And also we have to quickly wind up the session , we have about 5
minutes now Channel16:
Vineet, I have Yukon Feb CTP I am unable to deploy the CLR assemblies.
What security check points should I look at? Vineet
Gupta
: See if you have disables support for clr modules to be
loaded Channel16: how can I check that...? gaurav: my last question... any special update feature in sql
server 2k5 ? Vineet Gupta:
I dont know the exact syntax or the tool point from where you can
do that Channel16:
any ways thanx Vineet Gupta: is there something specific you are looking for in updates? Manik (Moderator):
Ok people, one last question and then we will wind up today's session Vineet Gupta: king, sql2k5 can run on 256 mb ram too, but that is not
realistic Manik (Moderator): Vineet , please take one last question gaurav:
ya.. like without restarting server, we sholuld be able to apply updates Vineet Gupta:
oh you are referring to security updates and hot patches. I thought
this was about the update statement. Well, updates in production environments
are not frequent. And though there is support for hot patching, it
really depends on the kind of patch - some patches may require a restart. king: thanks a lot Mr Vineet Manik
(Moderator): Thanks a lot Vineet. Manik
(Moderator):
Today's sessions was quite interesting gaurav: thanks sir Manik (Moderator):
We will see you next week again at the same time gaurav: bye from honeywell india Vineet
Gupta: On an earlier question of why you should migrate to sql2k5,
my answer would be: enhanced availability (mirroring), security enhancements
and sqlclr support Vineet Gupta:
thanks guys dnyanesh: thanks sir yamini: thanks sir dnyanesh:
bye Manik (Moderator): Next Week Vineet will take us through Scalability with
Sql Server 2005 pradeep_TP:
i m late to this chat session pradeep_TP:
can i know where i can get the chat script Vineet
Gupta:
drop in questions on sqlpass (sqlbag) Kalyan: what kind of login authontication secure to have a. windows
authontication b.Sql authothentication c.mixedmode ? Vineet
Gupta: i will try and answer questions there Manik
(Moderator):
The chat transcripts will be avaialble at the Chat site within 2 days Sawan: Thanx for the Informative Session pradeep_TP:
can i get the URL please Kalyan:
thanks pradeep_TP: URL to the chat site.. Manik (Moderator): http://www.microsoft.com/india/communities/chat/default.aspx pradeep_TP:
great thanks.!! Manik (Moderator):
this is the URL to the Chat site, all transcripts are put up here Manik (Moderator):
Ok folks , thanks again and bye Manik
(Moderator): Thanks a lot Vineet Kalyan: hey guys... u can add my chat id with ur msn as_kalyan@hotmail.com Manik (Moderator): it was a pleasure having you with us today
.
