Solved

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

Posted on 2006-11-07
16
236 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
ID: 17894708
>> 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
ID: 17898010
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
ID: 17911255
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 6

Author Comment

by:Mach1pro
ID: 18070853
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
ID: 18109854
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
ID: 18142493
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
ID: 18145105
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
ID: 18145176
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 18150630
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
ID: 18155512
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
ID: 18164921
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
ID: 18181683
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
ID: 18185874
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
ID: 18186520
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
ID: 18190775
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
ID: 18191297
Thanks! I've finally got it to connect using a login other than administrator.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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