• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2336
  • Last Modified:

Disabling Trusted Connection

I am trying to install MS Dynamics GP and after the initial install, it then asks for a user and password to the Dynamics SQL Server.  WHen I enter the user/pass for the users I created in the Dynamics Database in SQL 2000 it says that I can't because of 'trusted connections'.  I know they need to be disables and after reading all teh articles here and on the microsoft site, I am still scratching my head as to how to disable the trusted connections in the 2000 sql server to allow me to access the GP program. This is something I have banged my head over for too long so this will be worth 500 points.

adam
0
riser117
Asked:
riser117
  • 10
  • 10
  • 4
  • +1
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Trusted connections are associated with the Windows Authentication, So if you disables the Windows authentication and allow only SQL server authentication, you can do it
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
In order to chnage the authentication , use , EM->rightClick on the server ->Properties ->Security -> authentication
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
If our application uses DSN, then you can disable this option in the curresponding ODBC DSN entry
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
riser117Author Commented:
Okay, I went to the authentication section of security and it gave me a choice of windows only and windows and sql server.  I switched it to windows + sql server.  It then asked me for the sa password which i don't ever remember setting so I left it blank.  It then asked if I wanted to restart the sql server to make the changes.  I thought that it was taking too long so I stopped the sql server + agent myself (intending to restart it).  Now it says that I have a error 1069 that the sql server cannot start due to a login failure.  Where do I go now?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>Now it says that I have a error 1069 that the sql server cannot start due to a login failure.  Where do I go now?

Run  Start->run  Services.msc
find the service MsSQlserver
Move to Logon tab, and select this account, then start the service ...
0
 
riser117Author Commented:
It gives me the same message as before... error 1069 and then the same message about login failure.  The only thing I did different to get this message was switch the login under security from windows only to sql + windows.  Where to now?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
did u change your windows password, try browsing some othe user to start ...
0
 
riser117Author Commented:
I did not have a password for windows until last night when I finally put one on.  I did this after I installed sql server 2000 last week.  If this makes a difference then should I add another user to the computer/domain? I am pretty new at this, but I am a quick learner so if you tell me step-by-step what to do, it will be done.  Thanks for all your help.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
After changing the password, i think this is the first time you are restarting the server ?

Previously u were using the same username and the previous password, but since the SQL service has already started, it won't recognise the password change. Now since you restart the service, you need to put the new password for that particular user,in the same way that i mentioned in my previous post
0
 
riser117Author Commented:
I went through the same method that you mentioned in the previous post but it won't let me go to the properties after I right click the server because it is not running and it says: " connection failed. check sql server registration properties"
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Not that post,

Run  Start->run  Services.msc
find the service MsSQlserver
Move to Logon tab, and select this account, then start the service ...
0
 
riser117Author Commented:
Okay! That worked! When I go into Dynamics GP it still gives me the same issues with the trusted connection even though i changed it from windows only to windows + sql server... now what do i do?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
check wheter  Dynamics GP  application uses DSN, then you can disable this option in the curresponding ODBC DSN entry
0
 
riser117Author Commented:
When i went through the install manual, when i was setting up the system dns and click on with sql server to verify the authenticity of the user, in the user it has: Administrator and then when i went to put in the password that we just changed with the other exercise, it gave me the following message:
"Connection Failed:
SQL State: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]login failed for user: AIS\Administrator"

I tried both AIS\Administrator and Administrator with the same results... not sure where to go from here...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
On which user accout the sql server is starting (Start->run->services.msc->login )
is this the same as AIS\Administrator , otherwise try to change it and restrat the service
0
 
riser117Author Commented:
The user account the sql server is starting is indeed AIS\Administrator... where to now boss?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
hmm. I  am tired ... I think you must refer the installation manual.
Also check whether the application uses DSN to connect to the sql server (refer the manual)
0
 
riser117Author Commented:
the installation manual goes over that section with the ODBC but it doesn't go over if there are issues... i don't know anybody else that can help me on this.  what should i do? i'm guessing it's just a simple item that is preventing the login from connecting with the sql server...
0
 
riser117Author Commented:
is there anybody else that can help me disable the trusted connection?
0
 
nmcdermaidCommented:
What is the exact error message that GP gives you?

>>in the user it has: Administrator and then when i went to put in the password

There is some confusion here.

If you select 'Trusted Authentication' in the DSN, (PS this is 'DSN' not 'DNS') then you do not enter a user name or a password. Im guessing this is the mode that GP does not support.

If you select 'SQL Server authentication' in the DSN, then you need to enter a SQL server user, not a windows user. AIS\Administrator is a windows user.


So when you create your DSN for Great Plains, ensure:

1. It is using 'SQL Sevre Authentication'
2. Enter a valid SQL User, for example 'sa'



0
 
riser117Author Commented:
I don't remember ever setting an sa password - how do i reset it so I can then know what it is?
0
 
nmcdermaidCommented:
1. First try using a blank password with the sa login. Even though this is very naughty, many people leave it blank.
2. If that doesn't work, log into Enterprise Manager (using trusted security) go to the security tab, find the sa user and reset its password.

Do you understand the difference between Trusted (Windows) Security and SQL Security?


Back to your original issue you need to do two things:

1. Ensure that the server configuration is set to use windows AND sql server security, as per aneesh's suggestion.
2. Ensure that you pick 'with sql server authentication using a login id.......' in your DSN (but this should be in the manual)

0
 
vijay4sqlCommented:
-- YOU CAN NOT DISABLE THE WINDOWS (TRUSTED) AUTHENTICATION.

For auth .. we habe two options...
1. Windows
2. Mixed .. ie. Windows and SQL.

Now coming to the error message

"Connection Failed:
SQL State: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]login failed for user: AIS\Administrator"

-- Have we created login (in SQL) for "AIS\Administrator"....
-- If u logon to the box as "AIS\Administrator".. Can u connect to SQL?

thx
VJ

0
 
nmcdermaidCommented:
I think the issue here is that:

1. riser117 is using windows authentication but doesn't realise it
2. Great Plains does not support windows authentication.

Therefore the solution is to login using a SQL Account, rather than a windows account.
0
 
nmcdermaidCommented:
... but the clarification is appreciated as rereading this post, we hadn't made it very clear that you can't actually disable windows authentication.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 10
  • 10
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now