restricted user access

I want to restrict a machine so that it can only connect to an SQL Server 2000 machine on the network using one (maybe two) user identities.  If it tried to connect using any other existing user accounts it would be denied.  How would I go about doing that?
LVL 3
barryfandangoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

namasi_navaretnamCommented:

Connecting from an application or  connecting from Query Analyser?


0
barryfandangoAuthor Commented:
well it's from an application ostensibly, but i want to prevent any connection whatsoever as a layer of security.  this is a web server in our dmz, and if somebody managed to gain full control over it they should still be limited to the roles of my single web application user - that is, can't connect as SA or anybody else.
0
namasi_navaretnamCommented:
I do not know the answer.

If the connection is from an application then access can be restricted by checking the HOST_NAME

Declare @host_name
SELECT @host_name = HOST_NAME()

I so not know how the connection can be restricted otherwise. I am sure there are experts who can help.

:)

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

barryfandangoAuthor Commented:
Thanks anyways namasi.
0
TonylmillerCommented:
Just use Windows authentication for your database and give those users permission that you want.
0
barryfandangoAuthor Commented:
The problem with that is that our web server is in the dmz and doesn't belong to our windows 2000 domain.  So it's forced to connect with a SQL User Identity instead of windows.
0
LowfatspreadCommented:
Hi barryfandango,

a possible way is to have a scheduled job continually checking the connections ,
and then Killing any non authorised ones...
or raising the alarm and then monitoring
but of course if someone did manage to get the sa paasword ethc
then they may be able to detect and delete this...

and please maintain your open questions
4 open questions:
11/02/03 http://www.experts-exchange.com/Q_20785473.html "restricted user access"
08/07/03 http://www.experts-exchange.com/Q_20702822.html "CR9: Blob field Missing"
07/22/03 http://www.experts-exchange.com/Q_20686444.html "Crystal Report 9 .NET Assembly"
10/13/02 http://www.experts-exchange.com/Q_20373215.html "IIS New Install won't work"


Cheers!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barryfandangoAuthor Commented:
please post on my other open question "EE: Please list and detail my open questions for me."  you'll definitely get the points on that one.

anybody else?
0
Anthony PerkinsCommented:
>>anybody else? <<
You might find experts more responsive, if you took the time to maintain your old open questions.

Thanks,
Anthony
0
barryfandangoAuthor Commented:
Forgive my irritability on this subject, and allow me to share MHO.

As an expert who has answered a few myself, i couldn't care less about people's old questions.  It's not my job to worry about such things.  If EE was crippled by my unclosed questions i'm sure some adminsitrative system or individual would let me know.  Since i'm an expert, and not an administrator, i worry about helping people with their problems and not chastising them over trivialities.  In other words:  if i'm looking for advice about what to do with a couple of old unclosed questions, i'll post it and the experts can have at it.  Otherwise i wish people would stay more on topic.

Apologies lowfatspread for being short.  Combination of a bad day with this phenomenon that has been bugging me lately.
0
TonylmillerCommented:
Why don't you set up your users w/SQL usernames and passwords, and then have them log into your app?  Then your could pass their credentials through your connection string and only they could log in.  I've never done it quite this way, but it seems logical to me.

Tony
0
barryfandangoAuthor Commented:
Tony,

The situation i'm trying to proactively deal with here is one where a malicious user is able to gain complete control of my webserver in the dmz.  In that case, as my server sits right now, that malicious user could use the SA user account to wreak all sorts of havok on my database machine - assuming they could obtain a password or otherwise impersonate a powerful user.  I want to limit it so only the user account used by my application - called "aspnet_dbaccount" - can connect to the db machine, which limits their action to the stored procedures used normally by the web application.
0
barryfandangoAuthor Commented:
edit for clarity:
...
I want to limit it so that ** from my webserver in the dmz ** only the user account used by my application - called "aspnet_dbaccount" - can connect to the db machine, which limits their action to the stored procedures used normally by the web application.
0
russellshomeCommented:
Here are some precautions that will reduce risk of intrusion:
Disable the Named Pipes protocol
Change the TCP/IP port SQL server is listening on from 1433 to something else
Give the sa user an extremely long password full of unusual characters
Give all non-windows database accounts extremely long passwords full of unusual characters
Develop a COM or DotNET object to handle all database connections - specify the connection string within the compiled code (use obfuscator if you are using DotNET) so as to be inaccessible OR Encrypt the password before storing in web.config (Decrypt in your code)
Obfuscate all your code if using DotNET.
0
LowfatspreadCommented:
its ok...
i have them too ;-)

but
have you discarded my (on topic) suggestion?
0
Anthony PerkinsCommented:
>>As an expert who has answered a few myself, i couldn't care less about people's old questions.<<
That is fine with me, but perhaps we can convince you to care about your own.  They are after all your responsibility.

>>If EE was crippled by my unclosed questions i'm sure some adminsitrative system or individual would let me know. <<
Correct.  EE relies on volunteers like myself to clean up old questions.  Take a look at this topic area alone, there are well over 3K open questions.  I spend a cosniderable time over the weekend going through many of those, hence my vested interest.

But again you are right, while your questions are your responsibility, you can choose not to follow-up on them.  

Finally, let me quote from the EE Guidelines:
"Remember, the Expert helping you today is probably going to be helping you next time you post a question."

I wish you the very best of luck,
Anthony
0
barryfandangoAuthor Commented:
lowfatspread,

would it be possible to have a scheduled job that does the following?

for each current connection
   if connection's IP address is 123.123.123.1 and user not in (valid_user1, valid_user2) then
        terminateTheConnection()

And is this solution workable?  A script that connected to the database and wiped out some choice tables could run in a few moments.  This job would have to be scheduled to run _really_ often.
0
LowfatspreadCommented:
don't think you can do it on ip_address
but

this would check and kill......
not sure what resource consumption it would take....
you could probably run it every 10-15 secs ....

if the data is hyper critical then i presume your already investing in mirrored sites, off site log shipping etc...
if a hacker penetrates this far all you can try and do is limit the damage though...

 

create table #temp ( spid smallint --The system process ID.  
,ecid smallint --The execution context ID of a given thread associated with a specific SPID.
,status nchar(30) --The process status.
,loginame nchar(128) -- The login name associated with the particular process.
,hostname nchar(128) -- The host or computer name for each process.
,blk char(5) -- The system process ID for the blocking process, if one exists. Otherwise, this column is zero.
,dbname nchar(128) --The database used by the process.
,cmd nchar(16)
)

insert into #temp
exec master..sp_who

declare @killem varchar(100)
declare @die smallint

while Exists (select spid from #temp where spid >= @die and loginname not in (user1,user2,..)
  and hostname = 'TheHost' and spid <> @@SPID)
 begin
     select @die=spid from #temp where spid >= @die and loginname not in (user1,user2,..)
  and hostname = 'TheHost' and spid <> @@SPID
     set @killem = 'KILL ' + convert(varchar(10),@die)
     EXEC(@KILLEM)
 End  

0
LowfatspreadCommented:
your problem may be worse

since it appears that via OSQL/ISQL you can specify a workstation id to be
used...

look at the -h parameter...
not able to try myself currently..
0
barryfandangoAuthor Commented:
lowfatspread,

I'm going to give you the points, as you appear to know what you're doing.  I was hoping to find a simple function in MSSQL that would allow me to restrict certain IP's to certain users but by informing me that such a thing does not exist you have given me a valid answer anyways.  Thanks.

acperkins/lowfatspread,

i'm just cleaning up those old questions you mentioned earlier.  How do i go about deleting an open, unresolved question that has posts on it?  It appears there is no option to do so.
0
LowfatspreadCommented:
post in the support area
asking for the question to be either "PAQ'd" or deleted ?
i think.
0
Anthony PerkinsCommented:
>>i'm just cleaning up those old questions you mentioned earlier.  How do i go about deleting an open, unresolved question that has posts on it?  It appears there is no option to do so. <<
Here are your options:

Accept an Expert's Comment as the Answer (http://www.experts-exchange.com/help/closing.jsp#2)
Accept Comments from more than one Expert (a Split) (http://www.experts-exchange.com/help/closing.jsp#3)
Request a refund because you answered your own question (Refund/PAQ) (http://www.experts-exchange.com/help/closing.jsp#4)
Request a refund because no one answered your question (Delete) (http://www.experts-exchange.com/help/closing.jsp#5)
Abandon your question and leave the mess for someone else to clean up (http://www.experts-exchange.com/help/closing.jsp#12)

Anthony
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.