When connecting SAP Business One to SQL Server 2005, what is the

Question

we have SAP Business One - Fourth Shift Edition running here at a small manufacturing company. The consulting company that has come in to do the installation/implementation uses the "sa" id/pass to initially connect to the database to get the list of companies. From then on, I have to assume that its the sa id/pass that is being used to connect the client software to the database. Is this appropriate? I dont know where this data is being stored... as an ODBC connection? directly in the registry somewhere? Is it secure? Would it be better to set the users network ID in the database security and then use the "trusted connection" setting instead? Or do most people create a separate login in the database for each user and use that in the client settings?

seems like the easiest way would be to add the users network login to the sql server security so they can use the "trusted connection"... but then wouldn't that allow ANY software to connect to the database from that machine?

So anyways: what are the best-practices for setting this up?

Answer 1

That sa usage sounds like a recipe for disaster.

In most security models I have seen, regardless of how you connect, the first lookup SPs, views or tables are read-accessible to all authenticated users. Even if the application has a dedicated logon, it's not sa.

Without knowing more about SAPs limitations, I can't be sure, but we always tend to use Windows Authentication and Windows Active Directory Groups. Those groups are allowed in SQL Server roles. So all administration is done at the AD level. The DB is locked down according to those roles - even if an application has a SQL Server login or a domain login, it's going to be in one of the database roles we've created, named and granted rights appropriately.

Answer 2

That is very wrong, the sa account should not be used for general use.

A separate ('application' specific) user account should be used so that:

• Security can be applied on a per user (application) level
• If anything goes wrong with the application (e.g. it locks the user account), just that one account is effected and it is easy to diagnose issues

I also agree with Cade Roux's comments.

Answer 3

Guys,

thanks for replying.

Cade... I dont believe you're able to give roles to "applications" in windows... but you can assign the user that logged into the machine to a role... correct? So what you're saying is, if I had a userId of "MYDOMAIN/nick"... then in AD you would assign MYDOMAIN/nick to a group with other people that use this same app, and then in SQL Server you would add that group to security and assign it a role?

my concern is that if I sign into my machine with MYDOMAIN/nick... that activates my entire machine as "trusted" to sql server (through windows authenticatino)... so that means I could fire up Visual Studio and start building any app I want and potentially connect directly to the database and do whatever I'd like to it... which also means any other application I can download/install would potentially have access to that database... correct?

I learned that SAP client encrypts its connection info and stores it in the registry. So it would probably be best to create an application specific ID ("sapb1") and use that to connect all of the clients. the problem is that the SAP B1 add-ons can't use this connection... so we have to manage THOSE connections separately... which is a huge pain in the ass... and thus the "trusted connections" would make life much easier (no need to manage connection strings at all)... but then I worry about other applications getting into the database...

-blah

Answer 4

Cade... I dont believe you're able to give roles to "applications" in windows...

By that I think he means you assign roles to a user, then make the application use that user account.

So what you're saying is, if I had a userId of "MYDOMAIN/nick"... then in AD you would assign MYDOMAIN/nick to a group with other people that use this same app, and then in SQL Server you would add that group to security and assign it a role?

Correct.

my concern is that if I sign into my machine with MYDOMAIN/nick... that activates my entire machine as "trusted" to sql server (through windows authenticatino)... so that means I could fire up Visual Studio and start building any app I want and potentially connect directly to the database and do whatever I'd like to it... which also means any other application I can download/install would potentially have access to that database... correct?

Yes that is correct. Because you (MYDOMAIN/nick) are being trusted. SQL Server does not know what you are running on your PC.

However going back to your original question, the program that you are talking about should not connect at MYDOMAIN/nick, it should connect with the username MYDOMAIN/mycustomprogram. This is a user account just for this program. You could run the program from your PC, but in that case it will still use the username MYDOMAIN/mycustomprogram, not MYDOMAIN/nick.

You could then have a second program on your PC, which should then use a second username for authentication to the SQL server, e.g. MYDOMAIN/mycustomprogram2

So on the same PC you would then have:

• MYDOMAIN/nick (AD)
• MYDOMAIN/mycustomprogram (SQL or AD user)
• MYDOMAIN/mycustomprogram2 (SQL or AD user)

Using these custom usernames at an application level overrides the AD authentication.

It also means that if you have an issue with one of the 2 programs, or the program locks a user account, etc. it is easy to diagnose.

I learned that SAP client encrypts its connection info and stores it in the registry.

Which part of the connection are you talking about? I am not aware of anything being stored in this way.

Does this answer your questions?

Please vote up helpful answers ;-)

Answer 5

Sorry, I can't vote up... because this was my first question.. and I didn't register when I asked it (just asked anonymously)... and now I dont think I can attach my new registration to the old question. I KNOW, SUCK!

Answer 6

I think it really depends on the level of security you're willing to offer/maintain. In SAP Business One, creating a new login needs to add certain privileges like giving db_creator permission to the database and the SBO-Common, or access to read/write tables and specific access to stored procedures, which is kind of a hassle.

Anyway, it is really a bad practice to keep the sa login for the users, but you can create other users and just change the login user when you start the application. There's and installation guide in the software CD where you can see that information with a step-by-step guide to do that. If you don't have it, ask your partner!