DataBase - ODBC DSN Dynamics GP SYSTEM password

Asked By jk

08-Sep-08 10:32 AM

Is it possible to re-create the Dynamics GP ODBC connection using the SYSTEM
Login ID? If so, how do I determine the SYSTEM password?

Thanks, John

08-Sep-08 11:48 AM

If it is for logging on to GP, you cannot set up the Windows System (Windows
Authentication) Username/Password on a DSN, as GP requires Mixed Mode
Authentication and SQL SA (or DYNSA) login credentials.

You may want to wait till others get back with any info.

--
Regards,
Vaidy
http://vmdyngp.blogspot.com

08-Sep-08 11:37 PM

------=_NextPart_0001_6A0AB090
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Vaidy is correct, standard Microsoft Dynamics GP does not support Windows Authentication.

You might want to gave a look at the configurator AD product from http://www.gofastpath.com/.

David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics

mailto:David.Musgrave@online.microsoft.com
http://blogs.msdn.com/DevelopingForDynamicsGP

Any views contained within are my personal views and not necessarily Microsoft policy.
This posting is provided "AS IS" with no warranties, and confers no rights.

The links in this message may lead to third-party Web sites. Microsoft provides third-party resources to help you find customer
service and/or technical support resources. Information at these sites may change without notice. Microsoft is not responsible for the
content at any third-party Web sites and does not guarantee the accuracy of third-party information.

------=_NextPart_0001_6A0AB090
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang3081{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 Vaidy is correct, standard Microsoft Dynamics GP does not support Windows Authentication.
\par
\par You might want to gave a look at the configurator AD product from http://www.gofastpath.com/.
\par
\par David Musgrave [MSFT]
\par Escalation Engineer - Microsoft Dynamics GP
\par Microsoft Dynamics Support - Asia Pacific
\par
\par Microsoft Dynamics (formerly Microsoft Business Solutions)
\par http://www.microsoft.com/Dynamics
\par
\par mailto:David.Musgrave@online.microsoft.com
\par http://blogs.msdn.com/DevelopingForDynamicsGP
\par
\par Any views contained within are my personal views and not necessarily Microsoft policy.
\par This posting is provided "AS IS" with no warranties, and confers no rights.
\par
\par The links in this message may lead to third-party Web sites. Microsoft provides third-party resources to help you find customer service and/or technical support resources. Information at these sites may change without notice. Microsoft is not responsible for the content at any third-party Web sites and does not guarantee the accuracy of third-party information.
\par
\par }
------=_NextPart_0001_6A0AB090--

10-Sep-08 07:49 AM

Does that mean that if the ODBC connection installed by GP gets deleted I
have 2 options, re-install GP or use a different user name to manually create
my own connection?

Also, is GP repair an option?

Thanks, John

10-Sep-08 11:16 AM

JKC,

I am not quite clear what the issue is...creating an OBDC connection for the
GP application is probably one of the easiest steps in the whole
installation and takes about a minute.  And the user ID used to create the
connection makes no difference whatsoever in the actual application.  Why
would you spend time reinstalling or even trying a repair?

--
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html

15-Sep-08 08:22 AM

A consultant installed GP 10. I asked them for the Dynamics GP ODBC
connection password. The SQL server authentication user name is shown as
SYSTEM for the Dynamics GP ODBC connection. The consultant is telling me
is not a password that we have assigned it is done automatically." I am
trying to understand what the proper procedure is for manually creating the
Dynamics GP ODBC connection if it gets deleted.

02-Oct-08 01:43 PM

Victoria,

This KB Article  uses sa user. Right now my ODBC connection user is SYSTEM.
I do not know the password for SYSTEM user. Is there a way to restore the
ODBC connection using the SYSTEM user. Again, I did not set this up GP client
originally, it was installed from a template. So I am not sure how this
connection was created using the SYSTEM user.

Thanks, John

04-Oct-08 09:06 PM

jkc, I always set my ODBCs for GP using the server credentials - sa
and whatever the sa password is on the server.  The server sa
credentials are not stored in the connection - they are just used for
validation when performing the ODBC setup.

BTW, always make sure that the EXACT server name, including case, is
used in the ODBC connection as GP uses the server name entered in ODBC
setup to encrypt the user's password.  If the server name used to
setup the ODBC does not match the server name at the server exactly,
the user will not be able to login to GP because a validation error
will occur.

You shouldn't have to use the SYSTEM user to set the ODBC.  Use sa and
the sa password.

Hope this helps,

Frank Hamelly
MCP-GP, MCT, MVP
East Coast Dynamics
www.eastcoast-dynamics.com

02-Oct-08 04:53 PM

What Frank said!

--
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html
blog: www.victoriayudin.com

02-Oct-08 06:14 PM

Frank,
This is a common misconception.  Although you can use sa user to setup your
DSN, there is no requirement or benefit in doing so.
As you rightly said, any account that can authenticate to the SQL server
will do the job.  You cannot use a GP user's login to set up the DSN because
GP encrypts the password but if you create a login on the Server and give it
access to say the pubs database, you can use that login to setup your DSN.

I am quite sure that the server name has no bearing on the encrypted
password.  Here is why:
If that were true, I expect that the server name would also be part of the
decryption  key.
In that case, you would not be able to copy logins and passwords from one
server to another.

You can find a KB article that shows you how to copy logins from one server
to another when you need to move from one server to another.

The encryption algorithm is a closely guarded secret so I could be wrong.
GP might be using a fancy, one-way, asymmetric encryption algorithm.

HS



message

04-Oct-08 09:06 PM

HS,

Check KB 870416 regarding ODBC setup for GP.  The article directs to
use the server name that was assigned to the instance of SQL server.
The article does not explain why but one might assume that it has
something to do with password encryption.

The article also recommends to use the sa username and password,
although they are not required.

I have learned from experience that if the exact server name is not
used in the ODBC setup, password validation will fail for that user.
Whether the server name is part of the encryption algorithm, I'm not
sure but the server name is definitely being used in some manner in
validation of the user credentials.  I can't find the document but I
do believe I've read an MS document indicating that the password
encryption in fact does incorporate the server name.

Frank Hamelly
MCP-GP, MCT, MVP
East Coast Dynamics
www.eastcoast-dynamics.com

03-Oct-08 06:15 AM

Frank,
That is a generic article on how to setup the DSN.
On my test machine, I have three instances of SQL Server - the default
instance,  GPTEST that contains the test data for one client,
GPTEST\Client2 for another client and so on.  of course your DSN should
point to the right instance.
I recall setting up an alias with the IP address of the server,and using
that alias in the DSN and being able to connect.

I tried that just now - I created an alias called GP which pointed to the
default instance of GPTEST.
I was able to authenticate using sa but could not using a regular user
account.
You may be on to something.  I don't know what, though.

Now you have piqued  my curiosity.  I hope I don't waste hours getting to
the bottom of this.

HS



message

03-Oct-08 08:13 AM

I think this may be relevant:

http://groups.google.com/group/microsoft.public.greatplains/msg/ad3d2a13fe7b9023

If I understand this correctly, the whole issue is what the DSN has for the
Serve name on the machine where the GP user is created.  So....if you create
a GP user on you machine (no matter what user is used for authentication
when the DSN is created), then that GP user will always work on that
machine.  However, if the Server name is different in the DSN setup of
another machine, that GP user will not authenticate on that machine because
the server name is part of the password encryption.

However, just to circle back, this is a completely different issue from what
the OP was asking.  The answer to that is ANY SQL user ID and password that
will authenticate properly directly in SQL with enough permissions can be
used to create the ODBC.  This does not include any GP users, since they
will will only work inside GP.  Since the user ID and password used to
create the ODBC are not stored anywhere with the ODBC and have no relevance
whatsoever, whether you use 'sa' or something else simply does not matter -
use whatever user ID and password will work.

--
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html
blog: www.victoriayudin.com


message

04-Oct-08 09:06 PM

What Victoria said! :)

Frank Hamelly
MCP-GP, MCT, MVP
East Coast Dynamics
www.eastcoast-dynamics.com

03-Oct-08 09:58 PM

I recall before 9.0 was released that dev mentioned the server name was used
in the encryption.  The theory was that it was more secure that way because
even if you had the same user that enters the same password on 2 systems the
course that ended up the same as well.

That's why users had issues originally where the IT guy would create the
users on the server where the DSN had "." or "local" for the server name and
then the users (who had SERVERNAME) couldn't log in from their own systems
but it worked fine on the server.

I think thought that might have been changed to look for "." or "local" the
servername was substituted automatically.  Thought i saw that one time but
haven't checked.

Anyway, not sure if i'm spilling trade secrets here (doubt it) but I guess I
can "confirm" that this is my understanding of how it works.

patrick
dev support

--
This posting is provided "AS IS" with no warranties, and confers no rights.

08-Oct-08 12:59 AM

------=_NextPart_0001_80470D6C
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

For more information on password encryption have a look at my blog post:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/02/why-does-microsoft-dynamics-gp-encrypt-passwords.aspx

David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics

mailto:David.Musgrave@online.microsoft.com
http://blogs.msdn.com/DevelopingForDynamicsGP

Any views contained within are my personal views and not necessarily Microsoft policy.
This posting is provided "AS IS" with no warranties, and confers no rights.

------=_NextPart_0001_80470D6C
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang3081{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 For more information on password encryption have a look at my blog post:
\par
\par http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/02/why-does-microsoft-dynamics-gp-encrypt-passwords.aspx
\par
\par David Musgrave [MSFT]
\par Escalation Engineer - Microsoft Dynamics GP
\par Microsoft Dynamics Support - Asia Pacific
\par
\par Microsoft Dynamics (formerly Microsoft Business Solutions)
\par http://www.microsoft.com/Dynamics
\par
\par mailto:David.Musgrave@online.microsoft.com
\par http://blogs.msdn.com/DevelopingForDynamicsGP
\par
\par Any views contained within are my personal views and not necessarily Microsoft policy.
\par This posting is provided "AS IS" with no warranties, and confers no rights.
\par
\par }
------=_NextPart_0001_80470D6C--

08-Oct-08 05:52 PM

David,
Thanks for a detailed post on this.

The one problem with this that GPConnNet.dll is now totally useless.  If you
are developing integrations, I suggest you create a integrations user  who
is a member of DYNGRP and run your queries under that user's context.
Problem is what to do about that user's password.
Hard-coding those credentials into your addin is a bad, lousy idea but
storing it anywhere just makes it accessible to anybody who wants it.

I'm considering a complicated system , similar to a registration key.  The
credentials are stored as two registration keys as clear text.
one of those keys is a username and the other a password.
The administrator is given the unencrypted username and password to set up
the account in SQL server.
For additional security the user id and or password can change periodically,
but that may be overkill for a lot of users.

Comments, anyone?
HS