February 14, 2008 at 1:13 pm
I am charged with synching security specifics between two dissimilar database engines.
Here’s the environment:
SQL Server 2005 (SS2K5): This db is used strictly for client data and is administered by the client IT people.
Sybase SQL Anywhere 9.1 (SA): This db is used to store “metadata” to configure the UI, among other things, and is administered by our organization. It also contains some client data, (mostly legacy stuff.)
This is my task, which I assume will require some new db objects on both sides:
1. When a new user/password is added to the SA db, then that same user/password needs to (eventually) be added to the SS db.
2. When an existing user’s password is changed to the Sybase db, then that same user’s password needs to (eventually) be changed in the SS db.
3. When an existing user is dropped from SA db, then that same user needs to (eventually) be removed in the SS db.
In the future, (I predict):
1. The user’s group/role affiliations will need to (eventually) be synched between the two db as well. Currently there is only one group(SA) / role (SS), and all users are members, so this is not an immediate concern… but this can and will likely change.
2. When the IT people get around to adding users to the SS db, they may wish for this functionality to work in the other directions.
I qualify the above specs with “eventually” because I do not believe the synching processes need to happen immediately. This leaves solutions open to scheduled or parameterized events (SA) and/or jobs (SS).
Without going over what solutions I have tried out, (which have not worked to my satisfaction), I put this to you.
Any Suggestions?
Takauma
February 20, 2008 at 3:12 pm
this is what some of the suggestions I am thinking about when I read this:
1. When a new user/password is added to the SA db, then that same user/password needs to (eventually) be added to the SS db.
I would use SSIS with this. I would execute the 'Transfer Logins Task' I would set up the source database as Sybase and the destination as SQL. not knowing all your specs this should work.
2. When an existing user’s password is changed to the Sybase db, then that same user’s password needs to (eventually) be changed in the SS db.
Again in SSIS, you can tell Transfer Logins task that if exists override. (Under Properties)
3. When an existing user is dropped from SA db, then that same user needs to (eventually) be removed in the SS db.
Still in SSIS, run a Script Task or Execute SQL task to drop the logins
February 20, 2008 at 4:15 pm
This looks promising, jsheldon. My Thanks!
Keep in mind that the legacy server is not a true Sybase engine, but rather the Sybase SQL Anywhere (SQLA) product, formally known as WHATCOM SQL. Whereas Sybase Enterprise has much in common with SS structurally, SQLA is a pale emulation. I worry that this may make it more difficult for SSIS to address... Any thoughts?
I suppose the next step is confirming that the client is willing to install SSIS on one of there boxes.
Joel
Takauma
February 20, 2008 at 6:26 pm
If you have SSIS up and running do a Source Connection and look at the drop down...it should have an OLE or ODBC connection, any DB then should be able to connect...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply