restricted user access

Connecting from an application or  connecting from Query Analyser?

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.

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.

:)

Thanks anyways namasi.

Just use Windows authentication for your database and give those users permission that you want.

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.

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?

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

Thanks,
Anthony

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.

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

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.

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.

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.

its ok...
i have them too ;-)

but
have you discarded my (on topic) suggestion?

>>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

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.

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  

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..

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.

post in the support area
asking for the question to be either "PAQ'd" or deleted ?
i think.

>>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 (https://www.experts-exchange.com/help/closing.jsp#2)
Accept Comments from more than one Expert (a Split) (https://www.experts-exchange.com/help/closing.jsp#3)
Request a refund because you answered your own question (Refund/PAQ) (https://www.experts-exchange.com/help/closing.jsp#4)
Request a refund because no one answered your question (Delete) (https://www.experts-exchange.com/help/closing.jsp#5)
Abandon your question and leave the mess for someone else to clean up (https://www.experts-exchange.com/help/closing.jsp#12)

Anthony