BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  How to implement security on ADO MSSQL2000          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Stephen K. Miyasato Guest Posted: Mon Sep 15, 2003 10:20 am    Post subject: How to implement security on ADO MSSQL2000 I'm about to release my application that is written in MSSQL and on some parts of the database, I currently don't want uses to view certain parts of the data, except through the application. Presently I can use Access to look and query the data, which I currently wish to protect. I'm currently am using mixed mode i.e. sa with passwords. Can someone point me in the direction of either encrypting the data or implement a password schema? Is it "easy" or are there third party tools, books that allow on to do this. Do the stored procedures have to be encrypted also? Thanks very much for you help. Stephen K. Miyasato MDsync Back to top Finn Tolderlund Guest Posted: Mon Sep 15, 2003 10:29 am    Post subject: Re: How to implement security on ADO MSSQL2000 First of all, do not use sa to access your database. sa should only be used by the system administrator to maintain the database, it should not be used by normal users. Create a personal login for each user and make them use that instead, or let the users connect using windows authentication. -- Finn Tolderlund "Stephen K. Miyasato" <miyasat (AT) flex (DOT) com> wrote Quote: using mixed mode i.e. sa with passwords. Can someone point me in the direction of either encrypting the data or implement a password schema? Back to top Jeremy Collins Guest Posted: Mon Sep 15, 2003 11:01 am    Post subject: Re: How to implement security on ADO MSSQL2000 Stephen K. Miyasato wrote: Quote: I'm about to release my application that is written in MSSQL and on some parts of the database, I currently don't want uses to view certain parts of the data, except through the application. Presently I can use Access to look and query the data, which I currently wish to protect. I'm currently am using mixed mode i.e. sa with passwords. Can someone point me in the direction of either encrypting the data or implement a password schema? Is it "easy" or are there third party tools, books that allow on to do this. Do the stored procedures have to be encrypted also? SQL Server already has security, if you're unfamiliar a half-decent book will get you up to speed very quickly (it's not hard, but don't mess about by trial and error). And as Finn says, don't use "sa"! Why would you want to encrypt some data? Surely the sysadmins should be able to go in and fix any problems if need be. What if you're on holiday or decide to take up goat-herding? ;-) If there are data protection, or data ownership issues, then a well formed license or contract is better than obfuscation, IMO. -- jc Remove the -not from email Back to top Stephen K. Miyasato Guest Posted: Mon Sep 15, 2003 7:14 pm    Post subject: Re: How to implement security on ADO MSSQL2000 Thanks for the reply, I don't think that I could use Windows authentication as this is a Medical Application that has user logging built in. With Medical applications in general, several users are using the PC at different times and login in and out of Windows to change user can be a problems. Instead I'm using mixed mode authentication. I'm thinking of logging in with a string to the Datalink properties, send in the password by code and allow saving password (box checked). I could then remove the sa mode and probably assign dbo to another Admin user group? Does that sound right. Anyone has code to do that? Thanks for the help again, Stephen K. Miyasato I guess the best method is to use user login in and "Finn Tolderlund" <no (AT) spam (DOT) dk> wrote Quote: First of all, do not use sa to access your database. sa should only be used by the system administrator to maintain the database, it should not be used by normal users. Create a personal login for each user and make them use that instead, or let the users connect using windows authentication. -- Finn Tolderlund "Stephen K. Miyasato" <miyasat (AT) flex (DOT) com> wrote in message news:3f659267 (AT) newsgroups (DOT) borland.com... using mixed mode i.e. sa with passwords. Can someone point me in the direction of either encrypting the data or implement a password schema? Back to top Mike Walsh Guest Posted: Tue Sep 16, 2003 12:55 pm    Post subject: Re: How to implement security on ADO MSSQL2000 "Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote Quote: I can use Access to look and query the data, which I currently wish to protect. I'm currently am using mixed mode i.e. sa with passwords. Can someone point me in the direction of either encrypting the data or implement a password schema? Create an Application Role and assign all the objects you want to protect to that Role. You can trigger the Application Role from your application using sp_SetAppRole and providing the Application Role Password. -- Brian Bushay (TeamB) [email]Bbushay (AT) NMPLS (DOT) com[/email] We use this approach, and it seems to be a good way to get around security. What we do is we don't allow the users access to anything in the database, only have access to it and the app role has full access to everything. The only problem we had in setting this up was that we had to switch from the bde to ado. (That was a switch we had planned, but hadn't planned on implementing it yet) Mike Walsh Back to top Kevin Frevert Guest Posted: Wed Sep 17, 2003 3:20 am    Post subject: Re: How to implement security on ADO MSSQL2000 Just curious, why were you not able to use application roles and the BDE (we don't have any problems)? krf "Mike Walsh" <techs.msllib.com (AT) verizon (DOT) net> wrote Quote: We use this approach, and it seems to be a good way to get around security. What we do is we don't allow the users access to anything in the database, only have access to it and the app role has full access to everything. The only problem we had in setting this up was that we had to switch from the bde to ado. (That was a switch we had planned, but hadn't planned on implementing it yet) Mike Walsh Back to top Sarah Bram Guest Posted: Thu Sep 18, 2003 6:48 am    Post subject: Re: How to implement security on ADO MSSQL2000 For encryption of passwords, you may use TurboPower's LockBox which is now free at http://sourceforge.net/projects/tplockbox This is only for encrypting the passwords and not a substitute to the suggested AppRole. Regards, Sarah "Stephen K. Miyasato" <miyasat (AT) flex (DOT) com> wrote Quote: I'm about to release my application that is written in MSSQL and on some parts of the database, I currently don't want uses to view certain parts of the data, except through the application. Presently I can use Access to look and query the data, which I currently wish to protect. I'm currently am using mixed mode i.e. sa with passwords. Can someone point me in the direction of either encrypting the data or implement a password schema? Is it "easy" or are there third party tools, books that allow on to do this. Do the stored procedures have to be encrypted also? Thanks very much for you help. Stephen K. Miyasato MDsync Back to top Stephen K. Miyasato Guest Posted: Thu Sep 18, 2003 5:21 pm    Post subject: Re: How to implement security on ADO MSSQL2000 Thanks for the Help, How does one prepare the database so that sa does not have access. I have set up application role and made a new user. Seems that you cannot delete the sa account through enterprise manage. I then tried to detach the database and when I attached it again, I was still able to view the data through enterprise manager with Windows authentication. I wish to make it such that only the application can access the database based on application roles. Thanks very much again, Stephen K. Miyasato "Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote Quote: I can use Access to look and query the data, which I currently wish to protect. I'm currently am using mixed mode i.e. sa with passwords. Can someone point me in the direction of either encrypting the data or implement a password schema? Create an Application Role and assign all the objects you want to protect to that Role. You can trigger the Application Role from your application using sp_SetAppRole and providing the Application Role Password. -- Brian Bushay (TeamB) [email]Bbushay (AT) NMPLS (DOT) com[/email] Back to top Stephen K. Miyasato Guest Posted: Fri Sep 19, 2003 2:59 am    Post subject: Re: How to implement security on ADO MSSQL2000 Perhaps I should try and describe what I wish to do. I have developed a database with Propriety data. I wish to set this up using MSDE on different clients. It seem that when the database is attached at the "other site", the sa account login through Windows authentication is still able to look at all the data in that database. That is why I'm trying to prevent the Administrator from looking and accessing the database in their system. Am I missing something? Thanks for you help, Stephen K. Miyasato "Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote Quote: How does one prepare the database so that sa does not have access. I have set up application role and made a new user. Seems that you cannot delete the sa account through enterprise manage. I don't know how to delete SA and it sounds like a bad idea not to have an administrative account. Why can't you just set a password for it that no on knows? -- Brian Bushay (TeamB) [email]Bbushay (AT) NMPLS (DOT) com[/email] Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.