How can I protect user use other program to connect to MSSQL

suthipong
suthipong Member Posts: 14
edited 2008-10-17 in SQL General
Dear Sir
I have some problems about user connect to MS SQL with ODBC with others Software and to modify data in Navision ..
I need your help that how I can protect it ...

Thanks

Comments

  • kine
    kine Member Posts: 12,562
    TO be able to connect to SQL directly, someone must to add special permissions to this user. If you only add user into NAV, it will not grant permission to connect to the database and work with the data from outside NAV. It means that someone set the users as db_owner etc. Users need only Public permissions, which grants no rights to the data, only to logging into the DB... Check the SQL permission and remove any permissions set directly for the user accounts...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • kriki
    kriki Member, Moderator Posts: 9,121
    [Topic moved from 'NAV/Navision' forum to 'SQL General' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • suthipong
    suthipong Member Posts: 14
    I use with your solution but I still connect with ODBC ... with other Program ... How Can I protect ODBC to connect my Database SQL Server with other Software etc Access .
    Regards.
    Suthipong
  • kine
    kine Member Posts: 12,562
    If you are able to connect, it means that there are some permissions on SQL allowing you to read the data. Check server roles on the account. There must be no server role.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • suthipong
    suthipong Member Posts: 14
    Hi Kine
    I still problem about that user role I remove it all. and if I remove permit public only I still connect MS SQL by ODBC but in navision I can't use it the same user don't permit db_owner ...

    about this problem is : one user he can use access to connect MS SQL with his login to delete data in Navision .. so I try to protect to use other App to connect to navision by ODBC ..

    Regards.
    Suthipong
  • kine
    kine Member Posts: 12,562
    May be I misunderstand what you wrote, but I will write it again...

    The user must have only PUBLIC permissions to the database to be able to work with the NAV. Nothing else. No db_owner, no other server roles etc. This will prevent the user to do anything from outside, and will be able to log into NAV without problem.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • garak
    garak Member Posts: 3,263
    one simple (fast) solution is:
    Create a job on SQL Server agent over sys.dm_exec_sessions. If a application / defined user will connect (SPID > 50) to your defined database, and it's not NAV or Management Studio or SQLAgent or LogReader or some other needed, you can kill the session and log the application / user off.

    Regards
    Do you make it right, it works too!
  • suthipong
    suthipong Member Posts: 14
    Hi Garak
    your suggestion is to check sys.dm.exec_sessions but I can't find it in SQL 2000 .. and in SQL 2000 I found SPID more than 50 and SPID that I found is Navision connect to DBMS but I can't find my test that I have another application (Access 2007) to connect DBMS too and I can't find SPID of this ....
    Can you explain me more ...?
    Regards.
    Suthipong
  • p.willemse6
    p.willemse6 Member Posts: 216
    Maybe, maybe it would be wise to follow a basic SQL education. These questions really go back to basics of SQL...

Categories