C# Programming
05-16-2009, 10:16 AM
Scenario : client server application. vb6 client side application to Microsoft SQL server database.
all clients have registry keys to provide odbc connection to a database holding available environments.
Once a user has selected the environment they require the specific odbc string is supplied from this master to the specific db for the selected environment and the master plays no further part.
Problem : because every user selects from the same list all connections are listed under the same user in SQL making life very difficult to resolve locking issues that occure.
The use of windows integrated logins is not possible as i have no access to the application code and we need to prevent r/w access to the environment databases from say office applications.
Solution : I am therefore considering implementing a wrapper for the client application to generate individual sql logins and account in the master DB so that the connections can then be distinguished in SQL.
The wrapper will need to talk to a service that will have the relevant sql access to create / disable / enable the logins.
Question : with all this said can I please have relevant pointers / ideas / examples to first create this service that will need to talk to SQL via a dedicated admin account and also to clients on the network probably via IP using again probably XML.
I am not looking for spoon feeding as I enjoy learning in this hands on method just places to go / articles and examples that explain principles that I can then adapt.
Thanks for the assistance
Bryden
P.S I do realise that this will not be a walk in the park and that I could probably have posted this on a number of different forums but I plan on using c# unless advised differently.
all clients have registry keys to provide odbc connection to a database holding available environments.
Once a user has selected the environment they require the specific odbc string is supplied from this master to the specific db for the selected environment and the master plays no further part.
Problem : because every user selects from the same list all connections are listed under the same user in SQL making life very difficult to resolve locking issues that occure.
The use of windows integrated logins is not possible as i have no access to the application code and we need to prevent r/w access to the environment databases from say office applications.
Solution : I am therefore considering implementing a wrapper for the client application to generate individual sql logins and account in the master DB so that the connections can then be distinguished in SQL.
The wrapper will need to talk to a service that will have the relevant sql access to create / disable / enable the logins.
Question : with all this said can I please have relevant pointers / ideas / examples to first create this service that will need to talk to SQL via a dedicated admin account and also to clients on the network probably via IP using again probably XML.
I am not looking for spoon feeding as I enjoy learning in this hands on method just places to go / articles and examples that explain principles that I can then adapt.
Thanks for the assistance
Bryden
P.S I do realise that this will not be a walk in the park and that I could probably have posted this on a number of different forums but I plan on using c# unless advised differently.