Solved

restricted user access

Posted on 2003-11-02
22
617 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:barryfandango
  • 9
  • 5
  • 3
  • +3
22 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9666630

Connecting from an application or  connecting from Query Analyser?


0
 
LVL 3

Author Comment

by:barryfandango
ID: 9666647
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9666691
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
 
LVL 3

Author Comment

by:barryfandango
ID: 9666694
Thanks anyways namasi.
0
 
LVL 4

Expert Comment

by:Tonylmiller
ID: 9666761
Just use Windows authentication for your database and give those users permission that you want.
0
 
LVL 3

Author Comment

by:barryfandango
ID: 9666763
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 125 total points
ID: 9666851
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
 
LVL 3

Author Comment

by:barryfandango
ID: 9666921
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
 
LVL 75

Expert Comment

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

Thanks,
Anthony
0
 
LVL 3

Author Comment

by:barryfandango
ID: 9668185
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
 
LVL 4

Expert Comment

by:Tonylmiller
ID: 9668565
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 3

Author Comment

by:barryfandango
ID: 9668599
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
 
LVL 3

Author Comment

by:barryfandango
ID: 9668605
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
 
LVL 5

Expert Comment

by:russellshome
ID: 9669513
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9670091
its ok...
i have them too ;-)

but
have you discarded my (on topic) suggestion?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9671134
>>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
 
LVL 3

Author Comment

by:barryfandango
ID: 9673368
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9673546
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9690503
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
 
LVL 3

Author Comment

by:barryfandango
ID: 9716236
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9716272
post in the support area
asking for the question to be either "PAQ'd" or deleted ?
i think.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9719501
>>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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now