Solved

How to connect an Access 2000 adp to an SQL 2005 server

Posted on 2006-11-07
16
233 Views
Last Modified: 2008-01-16
Can anyone give specific instructions on how to set up a user and connect an Access 2000 adp to an SQL2005 server using Login Name rather than NTSecurity connection? I have done this with Server2000 using the sp_addlogin and sp_adduser SPs, and while there is no error message using these in 2005, it doesn't seem to have any effect
0
Comment
Question by:Mach1pro
  • 8
  • 8
16 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
>> it doesn't seem to have any effect

Is the issue that you can't add a SQL user or is it that you can't get the ADP to connect?

If the issue is connecting through the ADP: does it login to the database? Can you see the tables in the database? Is there any error If the If If the issue is creating the login: can you see it through Management Studio, can you create it through management studio?


Be aware that ADP's aren't suppotred with SQL2005 (though by all accounts they do work)


It may help if you set the compatability level of SQL2005 to 8.0


Though it probably doesn't have much to do with your particualr problem, according to SQL2005 help, you should use CREATE LOGIN instead of sp_addlogin. You should 'Avoid using this feature in new development work'



0
 
LVL 6

Author Comment

by:Mach1pro
Comment Utility
The adp will connect using NT security as long as the person logged into the server has Administrator rights. What I've seen happen behind the scenes in SQL2000, is that if a person without Admin rights opened an adp using NT security, then the SQL database objects become suffixed with (dbo), making any reference to the objects cause errors.  The problem we are having with SQL 2005, is that we have set up a Network user on the machine that hosts SQL and given the user Admin rights, but when we go to Enterprise Manager(I know its named different in 2005) this user never appears in the browse when selecting Logins. Any idea why? Or for that matter why other users on the system don't appear. They will appear when browsing for logins using 2000 Enterprise manager.
0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
Are you running a domain / AD there?

Are you saying that when you go to:

Security/Logins/Add new login

in Management Studio, then type the user name (Domain\Login) and hit 'Check Names' it can't find it?



If you want to try SQL Logins instead, add them through Management Studio rather than sp_addlogin etc. If you can see them in the Logins list, and they have access to the database then it should work through an ADP.





0
 
LVL 6

Author Comment

by:Mach1pro
Comment Utility
OK, I now have more convenient access to a server that has been setup to simulate my situation in order to resolve it, so lets try this one step at a time:
I have SQLServer2005 running on Microsoft server2003
I go into SQL 2005 server management studeo.
I am presented with the "Connect to Server"window which defaults to Windows Authentication mode.
If I switch this to SQL Server Authentication and put in a valid Server 2003 user account and try to connect, I get the following error message:  A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error:0 - No process is on the other end of the pioe.)(Microsoft SQL SErver, Error:233)
0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
You'll definitely need to get your SQL2005 install working first. The error implies that it is an install or service issue.

Does a windows authenticated login (through management studio) work?

How do you know its a valid SQL Server account, can someone else login with it?

Is this from the server machine or a client machine?


0
 
LVL 6

Author Comment

by:Mach1pro
Comment Utility
Right now I'm working strictly from the server machine.  the first hurdle I need to get over is to get SQL Server to run under a valid windows account rather than the builtin Administrators group. If I can get this part working and am still stuck I'll start a new question here in EE.  I know quite awhile back I set up SQL 2000 to run under a user account. It was basically selecting the user account and supplying the password when registering SQL.  But evidently I have missed a step or two somewhere along the way. So what I need is some explicit instructions on how to set up SQL2005 so that it will run under a user account on the server rather than defaulting to Administrator.
0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
I don't quite understand. Is your SQL Server install working under the Admin account? Can you now connect OK?
0
 
LVL 6

Author Comment

by:Mach1pro
Comment Utility
Yes, it works under the Administrator account. And the user I have set up is an administrator, but it won't connect under that account.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
So what can you see in SQL Server Management studio? Can you see your SQL User in there?
0
 
LVL 6

Author Comment

by:Mach1pro
Comment Utility
If I login to the server as Administrator, I can make the connection to sQL server.
I see my Login user name (NewUser) under the logins.  I see the user name under the Master database (newUser) with a default Schema of dbOwner.  But when I try to disconnect and logon to SqlServer using NewUser, I get the connection error message I described previously
0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
You also need to give that login access to the database that the ADP is trying to connect to.

You can use sp_grantdbaccess for this.


EXEC sp_grantdbaccess '<yourserverloginname>','<dbusername(generally the same as the server login name>'



I suggest you look up those sp's in the SQL2005 help and find out what the newer SQL2005 commands are. I would do it for you but my SQL2005 install keeps breaking.

Are you also altering the ADP connection screen to use that SQL user to connect?
0
 
LVL 6

Author Comment

by:Mach1pro
Comment Utility
Lets ignore the adp for now.  How can I get SQL Server Management to make a connection using SQL security rather than Windows security?
0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
According to this site:

http://msdn2.microsoft.com/en-us/library/ms175496.aspx

That particular error means you are trying to connect using named pipes but the server isn't set up to use it. Why it would be different for different users I don't know.

Anyway for starters try changing (and this is guessing cause I don't have a client install)

SQL Server Configuration Manager
> SQL Native Client Configuration
>> Client Protocols
>>> Disable named pipes and enable TCP/IP


and see if you get the same error message.

If that makes no difference we will attack it from the user angle.
0
 
LVL 6

Author Comment

by:Mach1pro
Comment Utility
I did what you suggested and now get the error: loging failed for user 'NameOfUser'. Tahe user is not associated with a trusted SQL connection.

Please note that after making the changes and only having TCP/IP enabled, Management Studio still connects okay using windows authentication.
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
Comment Utility
That means that you have configured your server to only allow trusted (windows) authentication. You need to reconfigure your server to allow SQL Connections.

Open Management studio, connect, right click on the server and press properties.

Press the security sub group and change server authentication from

Windows Authentication

to

SQL Server and windows authentication

0
 
LVL 6

Author Comment

by:Mach1pro
Comment Utility
Thanks! I've finally got it to connect using a login other than administrator.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SLQ View not updating 10 46
Sharepoint 3.0 migration 4 38
ASP SQL Syntax Duplicate Key 7 64
how to fix this error 14 45
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now