Post reply

Spoofing of login database name

  • January 5, 2006 at 10:38 am

    #91162

    I have a 3rd-party app that connects to a server via ODBC.

    Each username requires a database named "sde" as its initial connection point, from where it then gets the necessary information to switch to its user-specific database.

    I want 3 separate classes of user to be able to use their own user-specific database,  ideally on the same server.  This would require each user class to have their own dedicated SDE database,  but that would mean having the databases named differently (e.g. .SDE1, SDE2 and SDE3) which wouldn't work because the application code (which I don't have access to) is expecting the name to be SDE.

    So that I don't need to setup 3 separate database servers, can anyone think of a way of having the 3 SDE database setup, but somehow pointing the right user class at the right SDEn database, even though the ODBC connection string will always specify "SDE" as the initial database?

     

  • January 5, 2006 at 12:05 pm

    #613002

    Maybe.....set up three views in the SDE database. Give permissions to the proper class for each view. Have the connection check one common table for authentication and then from there identify which view is to be used.

    -SQLBill

  • January 5, 2006 at 12:14 pm

    #613009

    Hi Bill,

    Thanks for the reply, but sadly I don't think it's that simple.  I don't have access to the source code (if I did I could think of a few solutions to this), so I can't specify a view or table of my own to be checked.

    Somehow, I need some property of either the loginname or the ODBC connection that would allow automatic, transparent routing to an "aliased" database name - the particular alias in question being determined by the login account being used.

    Personally, I don't think there is a solution to this without having access to the source code, but I wanted to canvass as much opinion as I could to make sure.

    Any thoughts anybody?

  • January 6, 2006 at 1:50 am

    #613116

    You can name the server %servername%sde in dns with the correct ip address. This should resolve it straight away.

    If for whatever reason you're not using dns then just set a hostfile to reference the name.

  • January 6, 2006 at 4:32 am

    #613156

    I'd use 3 SQL Instances. The overhead isn't that much, so the if the Single Server Hardware will meet your needs/load it will probably be able to manage 3 instances.

    Dave J

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • January 6, 2006 at 4:43 am

    #613158

    The only problem with that, Dave, is that it makes it very difficult should we ever want to share data between the 3 databases.  I know, if we do want to share the data then they should probably be in the same database, never mind the same server,  but we're doing this for minimising of risk.

    To explain further ...

    We already have instances of this 3rd-party app which use a single database (which stores geographical data).  We're about to deploy a new instance in a much larger geographical region (bigger than we've ever done before), and because of the timescales involved we won't really have the luxury of full volume testing on datasets that could match reality.  To ensure that performance doesn't go off the scale exponentially, we're keeping the expected volume similar to that that we DO have experience of by splitting the data into 3 sub-regions, and placing each in its own database.

    So, logically, this data perhaps SHOULD be in a single database.  For purely pragmatic reasons it has been decided to split it.  We could use 3 separate instances as you suggest,  but the general feeling here is that that could create problems.

    Are there any admin-style problems that you can think of that come with multiple named instances?  (I've never worked with this config in a production setup)

    Phil

  • January 6, 2006 at 5:07 am

    #613164

    As you know, refering to an instance is done by Server\Instancename, the backslash may cause problems with a badly written app.  But if the app isn't instance aware, I wouldn't use it!

    You can share data as a linked server with the appropriate login rights, and if the \ really gets you down, set aliases , either SQL or DNS ones to 'point' to the instance.

    One gotcha with instances is that they use a different port other than 1433.  (If you think about it, they must). So I think you need to know the port number the instance is using when you setup the ODBC links.  Hang on., I'll try...

    No, you just type Server\Instance in the server name box.

    HTH

    Dave

     

     

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • January 6, 2006 at 7:19 am

    #613212

    You might want to look at the title "owner of database object" in the BOL.  You could create tables and other objects that have different meanings to different users in the same database.

    Normally, you create objects that are owned by "dbo", but that is not mandatory.  You can have two tables with the same name but different owners in the same database. 

    This might provide a solution that would help.  I'm not so much an expert on this sort of thing because I've never done anything like it.

    jg

     

  • January 6, 2006 at 8:46 am

    #613267

    So you can't make any changes to the initial database where everything is checked?

    Since the logins are unique, why do a 'initial database' at all? Create each login on the instance and give it a set default database. Then only allow access to that database for that login. When the connection is made, the user will be directed to 'their' database.

    -SQLBill

  • January 9, 2006 at 6:11 pm

    #613704

    How large is your dataset? You are probably much safer keeping the dataset intact rather than incurring all the problems you will introduce by splitting the data.

    ArcSDE is very fast with large datasets (We cover the state of Alaska). I don't think you will be sorry.

    Also you should consider upgrading to ArcSDE 9 and moving out of the sde database.

  • January 10, 2006 at 6:04 am

    #613767

    Thanks for that, Jean.  That's a very useful bit of information to know.  We're actually using ArcGSE, not SDE (although this still uses the SDE gateway as an entry point).  Do you have any experience of GSE, and if so would your previous comment about speed with large datasets still apply?

    Thanks,

    Phil

  • January 10, 2006 at 6:25 am

    #613775

    Actually, I got that wrong (I'm pretty new to Arc software).  We're using ArcGIS.  Same question still stands though

  • January 10, 2006 at 1:34 pm

    #613922

    You could create 3 different tables named sde, each with a different owner.  For example:

    dbUser1.sde

    dbUser2.sde

    dbUser3.sde

    If you're connecting with dbUser1 etc... then it should look for a table with that user as the owner.

  • January 11, 2006 at 4:52 am

    #614014

    if you remove the database name from the ODBC connection string or ODBC settings then set the default database for those logins to be SDE1, sDE2 etc -

    those users logging in will be directed to the relevant database - which you can configure in enterprise manager

    the only problem you may have is if the app calls tables in 3 or 4 part naming format

    i.e select * from SDE.dbo.mytable

    MVDBA

  • January 11, 2006 at 6:13 am

    #614028

    Thanks to everyone who has contributed to this.  In the end, since there appeared to be no guaranteed solution (as can be seen from the variety of replies that this discussion has come up with), we've decided to revert to the supported model of a single SDE database and hope that the performance will be adequate.  Jean Wylie's previous experience of this system covering large geographical areas has also added to our confidence for this approach.

    Thanks again everyone.

    Phil

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply