When connecting SAP Business One to SQL Server 2005
we have SAP Business One - Fourth Shift Edition working here in a small manufacturing company. The consulting company that started the installation / deployment uses the "sa" id / pass to first connect to the database to get a list of companies. From now on, I have to assume that it is sa id / pass which is used to connect the client software to the database. Is it appropriate? I don't know where this data is stored ... how is the ODBC connection? right in the registry somewhere? It's safe? Wouldn't it be better to set the users' network id in database security and then use the "trusted connection" setting instead? Or do most people create a separate database login for each user and use that in the client settings?
It seems like the simplest way would be to add the users network login to the sql server security so that they can use a "trusted connection" ... but then this would not allow ANY program to connect to the database from that machine
Anyway: What methods are best for customization?
This use of sound sounds like a recipe for disaster.
In most of the security models I've seen, no matter how you connect, the first search SPs, views, or tables are readable by all authenticated users. Even if the application has a dedicated login, it is not sa.
Without knowing more about SAP's limitations, I can't be sure, but we always tend to use Windows Authentication and Windows Active Directory Groups. These groups are allowed in SQL Server roles. Thus, all administration is performed at the AD level. The DB is locked according to these roles - even if the application has a SQL Server login or domain login, it will be in one of the database roles we created, named and granted rights accordingly.
source to share
This is very wrong, the sa account should not be used for general use.
You must use a separate (user account) for the user account in order to:
- Security can be applied at the user (application) level
- If something goes wrong with the application (for example, it locks the user account), it just runs one account and it's easy to diagnose problems.
I also agree with the comments by Cade Roux.
source to share
Cade ... I don't believe you can give roles to "applications" in windows ...
I think it means that you assign roles to a user and then force the application to use that user account.
So what are you saying, if I have a userId from "MYDOMAIN / nick" ... then in AD you assigned MYDOMAIN / nick to a group with other people who use the same application, and then in SQL Server you would add this group to security system and assigned a role to it?
Right.
my concern is that if i log into my machine with MYDOMAIN / nick ... which activates my whole machine as "trusted" on sql server (via windows authenticatino) ... so i can start visual studio and start build any application I want and potentially connect directly to the database and do whatever I would like ... which also means that any other application I can download / install could potentially have access to that database ... right?
Yes, that's right. Because you (MYDOMAIN / nick) are trusted. SQL Server doesn't know what you are using on your PC.
However, going back to the original question, the program you are talking about should not connect to MYDOMAIN / nick, it should connect to the username MYDOMAIN / mycustomprogram. This is a user account for this program only. You can run the program from your PC, but in this case it will still use the username MYDOMAIN / mycustomprogram, not MYDOMAIN / nick.
Then you could have a second program on your PC, which then has to use the second username to authenticate to the SQL server, for example. MYDOMAIN / mycustomprogram2
So, on the same PC, you will have:
- MYDOMAIN / nick (AD)
- MYDOMAIN / mycustomprogram (SQL or AD user)
- MYDOMAIN / mycustomprogram2 (SQL or AD user)
Using these custom usernames at the application level overrides AD authentication.
This also means that if you have a problem with one of the two programs, or a program locks a user account, etc., it is easy to diagnose it.
I found out that the SAP client encrypts the connection information and stores it in the registry.
What part of the connection are you talking about? I don't know if anything is stored this way.
Does this answer your questions?
Please vote for helpful answers; -)
source to share
I think it really depends on the level of security that you are willing to offer / serve. In SAP Business One, to create a new login, you need to add certain privileges, such as granting db_creator permission for the database and SBO-Common, or access to read / write tables and specific access to stored procedures, which is kind of a hassle.
Anyway, it is very bad practice to keep the sa username for users, but you can create other users and just change the login user when the application starts. There's also an installation guide on the software CD where you can see this information with a step-by-step guide to do this. If you don't have one, ask your partner!
source to share