By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,515 Members | 1,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,515 IT Pros & Developers. It's quick & easy.

Connect Access to SQL Server

P: 34
Hi,

I'm a new SQL Server user, and am having initial troule connecting my Access front end to my SQL Server tables in the back end. I have no problem doing it through ODBC on my computer, but on other users machines I get this error:

SQL State 28000
SQL Server error 18456
login failed

The only different between my machine and their's is that I have SQL Server Management Suite, and they have no SQL Server software loaded in. I assumed that they didn't need anything installed. What am I doing wrong?

Robin
Feb 1 '08 #1
Share this Question
Share on Google+
27 Replies


ck9663
Expert 2.5K+
P: 2,878
Hi,

I'm a new SQL Server user, and am having initial troule connecting my Access front end to my SQL Server tables in the back end. I have no problem doing it through ODBC on my computer, but on other users machines I get this error:

SQL State 28000
SQL Server error 18456
login failed

The only different between my machine and their's is that I have SQL Server Management Suite, and they have no SQL Server software loaded in. I assumed that they didn't need anything installed. What am I doing wrong?

Robin
try checking the setting of your sql connection. if you're developing in the same machine that you're sql server is, sometimes the server name is "(local)". that will not work once you deploy your application...if you're using odbc, you might need to create an odbc connection on each machine that you install it to...try to test the connection of your odbc after creating to make sure you configured it properly...

-- ck
Feb 2 '08 #2

Jim Doherty
Expert 100+
P: 897
Hi,

I'm a new SQL Server user, and am having initial troule connecting my Access front end to my SQL Server tables in the back end. I have no problem doing it through ODBC on my computer, but on other users machines I get this error:

SQL State 28000
SQL Server error 18456
login failed

The only different between my machine and their's is that I have SQL Server Management Suite, and they have no SQL Server software loaded in. I assumed that they didn't need anything installed. What am I doing wrong?

Robin
Hi Rag,

I have some routines here (a simple strategy if you like) to create your odbc in code if they don't exist on the machine from the access frontend. ie when your main form opens a simple checkmark to denote (odbc ready, something like that) so that if the checkmark come up as unticked you'd know the routine failed and that odbc drivers not on their machine kind of thing). How are you connecting? windows integrated security? Have you looked at using ADP files (or is your app fully developed in mdb) ADP's they don't use odbc instead they perform under (udl) universal datalink which is integrated into the adp (access data project interface) for speaking directly to SQL server

Jim :)
Feb 2 '08 #3

P: 34
Thanks for the tips. I've been thinking about using an ADP, but hadn't gotten to that point yet, maybe this is time to do so.

I'm connecting through ODBC, using SQL Server authentication and a login ID. I use the same login on my computer and a users, and on mine I can access, on theirs I cannot. The server is not local, but on our intranet.

Internal tech support said I should embed the SQL connection string into my front end. Do you think they mean using ADODB?

Thanks


Hi Rag,

I have some routines here (a simple strategy if you like) to create your odbc in code if they don't exist on the machine from the access frontend. ie when your main form opens a simple checkmark to denote (odbc ready, something like that) so that if the checkmark come up as unticked you'd know the routine failed and that odbc drivers not on their machine kind of thing). How are you connecting? windows integrated security? Have you looked at using ADP files (or is your app fully developed in mdb) ADP's they don't use odbc instead they perform under (udl) universal datalink which is integrated into the adp (access data project interface) for speaking directly to SQL server

Jim :)
Feb 4 '08 #4

P: 34
I've made the ADP, and now am going through my vba code to adapt. I've been using DAO, not ADO (still unfamiliar with ADO), and I see the DAO isn't working anymore. Is ADO the language to use with ADPs then?


Hi Rag,

I have some routines here (a simple strategy if you like) to create your odbc in code if they don't exist on the machine from the access frontend. ie when your main form opens a simple checkmark to denote (odbc ready, something like that) so that if the checkmark come up as unticked you'd know the routine failed and that odbc drivers not on their machine kind of thing). How are you connecting? windows integrated security? Have you looked at using ADP files (or is your app fully developed in mdb) ADP's they don't use odbc instead they perform under (udl) universal datalink which is integrated into the adp (access data project interface) for speaking directly to SQL server

Jim :)
Feb 4 '08 #5

Jim Doherty
Expert 100+
P: 897
I've made the ADP, and now am going through my vba code to adapt. I've been using DAO, not ADO (still unfamiliar with ADO), and I see the DAO isn't working anymore. Is ADO the language to use with ADPs then?
If you are predominently working with recordsets in code yes use ADO.

If you NEED local tables ie on the client you might want to stick with your mdb format DAO and ODBC but if 'not' at least try out the ADP. I use ADP file nearly all the time to communicate with SQL Server they have form properties ie: inputparameters, resynch, serverfilter amongst others designed for working with SQL server. As for the connection embedded bit in your post? you will use ADODB provider for SQL Server driver with recordsets and yes you can hardcode it if you like as a string into your application if you want to open and close separate connections

Standard Security:
Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog= YourDatabaseName;UserId=YourUsername;Password=Your Password;

Trusted connection:
Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=YourDatabaseName;Integrated Security=SSPI;


Your ADP will have a persistent connection to the Server in the universal data link that is prebuilt with the ADP so referencing a connection in code for your recordset is a simple matter of stating

Dim conn as ADODB.Connection
Set conn= CurrentProject.Connection


Is there any reason why you cannot switch to windows integrated security if you are on a network your network knows who you are already when you logon to the network. SQL server can feed off that authenticate against it returning strings to your app like system_user
to use in any recordset data handling routines which in Access would equate to the CurrentUser function when working in a secure environment. In addition you wouldnt have to concern yourself with the password bit because thats built into the network arrangements...have look at the connection strings above...password is not required on the integrated security connection string

Hope this helps

Jim :)
Feb 4 '08 #6

P: 34
I think I'll go with the ADP method, thanks for the clarity on the connection strings. Taking a while to fix bugs now, though, as I have to make sure my old access sql strings are valid in sql server. I'm running into some other issues now with Access, subforms now coming up blank when previously they worked fine, and on some of them I get the nebulous error "Syntax error or access violation".



If you are predominently working with recordsets in code yes use ADO.

If you NEED local tables ie on the client you might want to stick with your mdb format DAO and ODBC but if 'not' at least try out the ADP. I use ADP file nearly all the time to communicate with SQL Server they have form properties ie: inputparameters, resynch, serverfilter amongst others designed for working with SQL server. As for the connection embedded bit in your post? you will use ADODB provider for SQL Server driver with recordsets and yes you can hardcode it if you like as a string into your application if you want to open and close separate connections

Standard Security:
Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog= YourDatabaseName;UserId=YourUsername;Password=Your Password;

Trusted connection:
Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=YourDatabaseName;Integrated Security=SSPI;


Your ADP will have a persistent connection to the Server in the universal data link that is prebuilt with the ADP so referencing a connection in code for your recordset is a simple matter of stating

Dim conn as ADODB.Connection
Set conn= CurrentProject.Connection


Is there any reason why you cannot switch to windows integrated security if you are on a network your network knows who you are already when you logon to the network. SQL server can feed off that authenticate against it returning strings to your app like system_user
to use in any recordset data handling routines which in Access would equate to the CurrentUser function when working in a secure environment. In addition you wouldnt have to concern yourself with the password bit because thats built into the network arrangements...have look at the connection strings above...password is not required on the integrated security connection string

Hope this helps

Jim :)
Feb 4 '08 #7

ck9663
Expert 2.5K+
P: 2,878
most write-ups and white papers are recommending mixed authentication as well. for ease of management and integrated security within the windows infrastructure..

-- ck
Feb 4 '08 #8

Jim Doherty
Expert 100+
P: 897
most write-ups and white papers are recommending mixed authentication as well. for ease of management and integrated security within the windows infrastructure..

-- ck
Spot on ck I should have mentioned that on the server side :) and RAG you won't be disappointed with the ADP so any effort 'will' be worth your while. Yes....... you'll get some rather tiresome issues smacking you in the face but they are and will be rectifiable and during the process you will doubltess see some "hmmmm nice! couldn't do that with an mdb" features.

Jim :)
Feb 4 '08 #9

P: 34
I'm walking through it all now, close to finished with the ADP conversion. Didn't realize how much coding had to be changed for SQL Server and ADO, and how many little things I had to rewrite (all my FindRecords had to be reworked).

One question about security. To use windows integrated security, how do I set that up in the SQL Server? Do I need to create a new user with the windows username and passwords for every use? Or is there some easier way? I have like 25 users.

Robin
Feb 5 '08 #10

ck9663
Expert 2.5K+
P: 2,878
I'm walking through it all now, close to finished with the ADP conversion. Didn't realize how much coding had to be changed for SQL Server and ADO, and how many little things I had to rewrite (all my FindRecords had to be reworked).

One question about security. To use windows integrated security, how do I set that up in the SQL Server? Do I need to create a new user with the windows username and passwords for every use? Or is there some easier way? I have like 25 users.

Robin
no you don't need to create each user. you just have to point which domain should the sql server get the account credentials from. you still have to grant all those users (DOMAIN\uname) with the necessary rights....

-- ck
Feb 5 '08 #11

P: 34
Good, looking into that now.

Another question, I've based some forms and subforms on views, but am finding that views based on multiple tables cannot be updated. This creates a problem for my forms that are for data entry. Any suggestions for ways to get around that? In my mdb file I could create a recordsource based off of a query, I was getting errors when I did that in the adp.

R

no you don't need to create each user. you just have to point which domain should the sql server get the account credentials from. you still have to grant all those users (DOMAIN\uname) with the necessary rights....

-- ck
Feb 6 '08 #12

ck9663
Expert 2.5K+
P: 2,878
Good, looking into that now.

Another question, I've based some forms and subforms on views, but am finding that views based on multiple tables cannot be updated. This creates a problem for my forms that are for data entry. Any suggestions for ways to get around that? In my mdb file I could create a recordsource based off of a query, I was getting errors when I did that in the adp.

R
i don't know about ADP. but an updateable view is a little tricky...if the error is saying you can not update the actual table, you just did not define the proper rights on the object...you have to specifically say that this user or group have the necessary rights on a db


-- ck
Feb 6 '08 #13

Jim Doherty
Expert 100+
P: 897
Good, looking into that now.

Another question, I've based some forms and subforms on views, but am finding that views based on multiple tables cannot be updated. This creates a problem for my forms that are for data entry. Any suggestions for ways to get around that? In my mdb file I could create a recordsource based off of a query, I was getting errors when I did that in the adp.

R
You can only edit the one side of a two table join. (and you define the 'Unique table' in the forms property for this purpose). As you realise a third table will result in an uneditable view

Look for examples of where you can embed 'user defined functions' within a view to achieve that dataset where you might otherwise have to rely on a join to a third table to return a particular column of data. Your view remains 'editable' in these circumstances.

Its hard to give you an example specifically for your needs without knowing the makeup of your system.

If you really get stuck on this PM me with your email (and timezone) and I will invite you to my desktop via remote software and visually demonstrate to you what I mean. I have forms that use views where the essence of the dataset returned by the view is comprised of multiple tables and involve embedded user defined functions. It isnt a workaround its a question of knowing how to build the view to achieve what you actually need.

Regards

Jim:)
Feb 6 '08 #14

P: 34
Great info. I'm doing some research into it now... Chances are I'll take you up on the remote access, as I'm still learning to navegate SQL Server Management Studio.

You mention the "unique table" the forms property, in Access. I defined that but the recordset is still not editable. Do I have to look at how the View is constructed?

You can only edit the one side of a two table join. (and you define the 'Unique table' in the forms property for this purpose). As you realise a third table will result in an uneditable view

Look for examples of where you can embed 'user defined functions' within a view to achieve that dataset where you might otherwise have to rely on a join to a third table to return a particular column of data. Your view remains 'editable' in these circumstances.

Its hard to give you an example specifically for your needs without knowing the makeup of your system.

If you really get stuck on this PM me with your email (and timezone) and I will invite you to my desktop via remote software and visually demonstrate to you what I mean. I have forms that use views where the essence of the dataset returned by the view is comprised of multiple tables and involve embedded user defined functions. It isnt a workaround its a question of knowing how to build the view to achieve what you actually need.

Regards

Jim:)
Feb 7 '08 #15

Jim Doherty
Expert 100+
P: 897
Great info. I'm doing some research into it now... Chances are I'll take you up on the remote access, as I'm still learning to navegate SQL Server Management Studio.

You mention the "unique table" the forms property, in Access. I defined that but the recordset is still not editable. Do I have to look at how the View is constructed?
Yes you do I am afraid......... and in many ways you can end up with a completely different view/query in SQL server when compared to the way it was, or might have been in Access, different beast so different requirements. Its going to sound dumb I know but also the simplest things get overlooked here.

For instance if you have a two table join and have defined the Unique table that allows the edits and find you 'still' you cannot enter records you had better check that you have your primary key set, because without that, you cannot edit anyway!! The times I have come across that simple build mistake as the cause for the unavailable edits is quite a lot actually.

To guide you through this would potentially wear my fingers away here its much easier seeing it done visually hence the remote access thing i spoke off. I am sure you'll say to yourself "of course" when you see for yourself rather than typing back and forth with all the questions that one could quite easily raise on this.

Jim :)
Feb 7 '08 #16

P: 34
Great, let's do remote access if you can. I'm in NYC, and could do anytime from 8:30 to 4:30 pm. It's probably best to communicate this info via personal email, rather than the forum. My email is: rghertne@health.nyc.gov.

Robin

Yes you do I am afraid......... and in many ways you can end up with a completely different view/query in SQL server when compared to the way it was, or might have been in Access, different beast so different requirements. Its going to sound dumb I know but also the simplest things get overlooked here.

For instance if you have a two table join and have defined the Unique table that allows the edits and find you 'still' you cannot enter records you had better check that you have your primary key set, because without that, you cannot edit anyway!! The times I have come across that simple build mistake as the cause for the unavailable edits is quite a lot actually.

To guide you through this would potentially wear my fingers away here its much easier seeing it done visually hence the remote access thing i spoke off. I am sure you'll say to yourself "of course" when you see for yourself rather than typing back and forth with all the questions that one could quite easily raise on this.

Jim :)
Feb 8 '08 #17

P: 34
Thanks Jim for the help. I realized I didn't get your email, I wanted to ask you a quick question about udfs, I'm getting a "is not a recognized built-in function name" error. Maybe you could email me the sql for one of your views that incorporates a udf that we looked at?

Thanks!
Robin
Feb 8 '08 #18

Jim Doherty
Expert 100+
P: 897
Thanks Jim for the help. I realized I didn't get your email, I wanted to ask you a quick question about udfs, I'm getting a "is not a recognized built-in function name" error. Maybe you could email me the sql for one of your views that incorporates a udf that we looked at?

Thanks!
Robin

Hi RAG,

To replicate an useage of a UDF embedded in an SQL view have a look at this a thread that I contributed to that provides for a working example that you can follow and replicate for yourself

http://www.thescripts.com/forum/thread766999.html

Regards

Jim :)
Feb 9 '08 #19

P: 34
OK, so I got the domain name from my IT department, and my server is in mixed mode. I want to add my 20 users through Windows Authentication. I'm in SQL server 2005, management studio. When I go to create a new login, for "Login Name", I would enter DOMAIN\username for each person (where username is their Windows username), select their default database, and then go to that database and add them as a user and define their role? Or do I just enter the domain name DOMAIN\?




no you don't need to create each user. you just have to point which domain should the sql server get the account credentials from. you still have to grant all those users (DOMAIN\uname) with the necessary rights....

-- ck
Feb 12 '08 #20

Jim Doherty
Expert 100+
P: 897
OK, so I got the domain name from my IT department, and my server is in mixed mode. I want to add my 20 users through Windows Authentication. I'm in SQL server 2005, management studio. When I go to create a new login, for "Login Name", I would enter DOMAIN\username for each person (where username is their Windows username), select their default database, and then go to that database and add them as a user and define their role? Or do I just enter the domain name DOMAIN\?

When you add your user..... look at it from this perspective Your user now exists as a user on the SQL server


Your users will be listed under 'security' node 'logins' sub node. Once a user is listed there you define what databases the user can have access to.

So create your users first, then grant them access to the databases you intend them to have access to. (THAT WAY ROUND) as opposed to going to a specific database and adding a user that way. This is relevant when assigning permissions for 'GRANT ACCESS' maybe for multiple databases.

Double clicking on each user name brings up the dialogue where you will see then that it is a simple question of ticking which databases they can have access to and under what role they will be assigned. Each user can if required have more than one role but be careful if you do this not to usurp or undermine the persmission of one role by assigning to another having permissions at a higher or lower level.


Create your relevant roles, define permissions for the role, then add your users to the role. So.... if you have a role of READWRITER the role having specific permissions to 'do' or 'not do' something with the objects or data then your users inherit those permissions for the role because they are in that role.

You can go adding a 'windows group' as a user but all users in the windows group have the permissions assigned to the windows group. OK for a generic entry where permissions are not vitallly critical because you control them in the app maybe........but to be more precise on this and also to have advantage of functions such as system_user (same as currentuser in Access where you might audit who does what with the data based on their login credentials.

You might want to just therefore simply tighten up by adding your users to each of your defined roles and control your permissions through roles not each user...much easier than assigning permissions to EACH individual user (you,d be there till next new year ticking boxes otherwise).

Trust this makes sense to you. I'm sure you'll play with it until you get the hang of how it works etc etc


Jim :)
Feb 12 '08 #21

ck9663
Expert 2.5K+
P: 2,878
I'd go with Jim with this one with respect to defining roles. It's one way to efficiently manage your sql server. Before you do everything, think of the roles of your users (not the user themselves). Try putting it in a worksheet so that you can properly match the user with their respective role. Then you can add the user to their respective role properly.

Good luck.

-- CK
Feb 12 '08 #22

P: 34
That's sound advice. So I'll add my users one at a time and ascribe them roles. Roles seem to be fixed, though, correct? I cannot add a new one. Doing a little research, I read that by playing around with schemas one could control which db objects users have access to, which I could see coming in handy at some point. But defining the permissions of what a user can and cannot do (eg edit) is done through roles, correct?

My problem now is, though, it seems I don't have permission to create users or assign roles! Argh, foiled by government bureaucracy. I guess I'll have to talk to IT and give them a list of users, and have them do the legwork.

Thanks guys for the help, slowly but surely I'll make it.
Feb 12 '08 #23

ck9663
Expert 2.5K+
P: 2,878
I think you can CREATE ROLE

-- CK
Feb 12 '08 #24

P: 34
Met with my DBA. They wouldn't give me permissions to add users or create roles, but suggested that in the front end I control that using windows system functions to determine who the user is. Now I'm searching for the vba code that will enable me to do that, but haven't found anything yet. Anyone know how to access that?
Feb 19 '08 #25

Jim Doherty
Expert 100+
P: 897
Met with my DBA. They wouldn't give me permissions to add users or create roles, but suggested that in the front end I control that using windows system functions to determine who the user is. Now I'm searching for the vba code that will enable me to do that, but haven't found anything yet. Anyone know how to access that?
Sounds like someone cannot be bothered with the overhead of administering a server properly to me... I don't personally do needless workarounds so maybe some else can help.

Regards

Jim :)
Feb 19 '08 #26

P: 4
this may be of some help.

http://www.mssqltips.com/tip.asp?tip=1437
Feb 19 '08 #27

ck9663
Expert 2.5K+
P: 2,878
Met with my DBA. They wouldn't give me permissions to add users or create roles, but suggested that in the front end I control that using windows system functions to determine who the user is. Now I'm searching for the vba code that will enable me to do that, but haven't found anything yet. Anyone know how to access that?

Your DBA is saying, you login using the username as setup on your apps or on your ODBC. On your front-end, you have to grab the username of the user. Login on your sql-server on a generic (could be your) user/pass. You now have to check if that username that you grab is authorized to use your system, maybe against a table with all authorized users and their password. If they are, you allow them to use it. If not, you prompt an error.

You don't have to request their username, maybe just their password. If you go to Command Line and type SET <Enter>, you'll see an environmental variable called USERNAME and USERDOMAIN. You can just use that. How to get those values? Well that would depend on your apps.

Happy coding.

-- CK
Feb 19 '08 #28

Post your reply

Sign in to post your reply or Sign up for a free account.