I am in need of some assistance regarding SQL Server ODBC links and security. This is for a project at work...
1) I have an MDB that has linked tables through ODBC to a SQL Server 2000 database. This is how I chose to write this multi-user application (instead of the ADP method).
2) My IT department created a user ID that the application will use to connect to the database. They chose this method instead of having to create many user accounts with varying permissions. I have programmed permissions within the application.
3) After linking the tables through ODBC, it defaults to my Windows User ID for the connection. Because of this, I have used a connection string with the application's User ID/Password and perform a RefreshLink on all linked table definitions on startup to ensure valid connection.
This is successful, but my IT department is seeing that another user is attempting access to the SQL Server database--the username in the LDB/MDW file! This is what I don't understand. If I open the database without going through workgroup security, the word "Admin" is passed and the authentication fails. If my users would login through the MDW file, their user IDs would be passed and again, authentication will fail as they are not valid users (only the application ID would be valid).
What is really going on here? :confused: How can I prevent the "Microsoft Access User ID" from being passed through the ODBC link?
Here is a sample of the connection string I'm using:
Driver={SQL Server};Server=ServerName;PersistSecurityInfo=Yes; AnsiNPW=No;Database=DBName;Uid=Username;Pwd=Passwo rd;
We are sharing an Access 2003 database amongst two departments that contains twelve (12) tables that are SQL Server linked tables. Nine (9) of the tables work just fine for people in both departments. Three (3) of the tables cannot be opened until the user refreshes the link(s) using their ODBC DSN.
We do not understand why nine of the tables work without issue but three will not.
Has anyone ever run into this issue before? Does anyone have any ideas on how to resolve this issue?
people can easily bypass the startup 'security' by holding shift. In that way they can see and edit all the tables that are linked to the back-end. Same goes for mde files.
Is there an easy way to prevent users from being able to enter tables. Like a simple password protection without having to set up user accounts and user level security?
In Access2010 I have created a linked table to a SQLServerExpress database.I have a user in SQLserverExpress with only reading rights.This linked table is used in a form which only shows the information. When opening the form, it prompts for the SQLserverExpress username and password.How can I configure the linked table so that it logs in automatically with the SQL user I prefer so that the user does not need to enter a user / password?
I have a fe that has 5 tables linked to a backend db on the server.I want to add a new table to the fe that is linked to a second db on the server? What I have tried.I have created the tbl_called in the fe.when I right click on it and go to Linked Table Manager I do not see it on the list. The only tables in the list are the 5 I linked before. Why is tbl_called not showing up??
I have a ms access application in Front End and linked tables to sql server 2008 r2 in back end.. every time I open my application the linked tables ask to me for the user and password of the sql server user of the odbc conection. I want to connect to my sql database without login every time the user open the application, I know that can be possible if I start session from vba code...
I've placed my database on our office server. The database has a security file attached to it and must be launched via a shortcut.
I've copied the shortcut on to the desktop of another machine, and when I launch the short cut, it prompts for the password and all that, but won't open the file. You get a message saying something like: "File is not on the Intranet or may not be from a trusted source. Could not open this file at this time."
I've create an ms access security workgroup and put .mdb and .mdw file to a folder under any acount on a server. When these files in my local machine it work properly. But when these files on server I myself cannot open .mdb file. How to make ms access security on a server ?
I have just started working with a team on a database that is available to everyone in this office. We have tried to lock it out and assign permissions by using workgroups, running security wizard, giving users no permissions, and deleting the Admin user, however none of this has sealed the database. The workgroup file is seperate folder on the server and the shortcut we make people use links to that workgroup. However Access is still defaulting to seperate workgroups on users personal computers. Is there anyway we can prevent this? Thanks.
Is it same thing about speed and other issues to have ADP application that works with SQLserver and to have a MDB (or MDE) application that has no querys but works with sql views instead?
My problem is I need to make it easy for my users by authenticating themselves once when they open the database. User-level security is adequate for that job, but when I need to use the authentication information for connection string, I'm a bit antsy about storing it somewhere in Access, as I understand that passwords shouldn't be stored in the database itself (and as far as I can tell, it's not even encrypted or hashed!)
I am wondering if converting it to MDE would be sufficient to encrypt the password or will it still be apparent using a hex editor? I also know that Jet can try to use its own security to authenicate to the back-end, but not sure what I need to do to prompt Jet to pass along the credentials.
Alternatively, would it be better to somehow implement login using backend's security model, if even possible?
Hi all, wonder if anyone can assist me with this the SQL Server security/write (?) issue. The SQL Server Admin has created a table for our Dept. to use and via SQL Server I can get access to it using the username and password she supplied to me. I can also link to this table in Access 2002 via ODBC which is the way I am using to write to SQL server tables. I have developed an Access application to allow one of our staff to be able to write data to the SQL Server table at a click of a button, however when I open the table the 'add new record' button is greyed out, I can not add any data to the table. I explained this problem to the Admin as well as the way in which we access the table and she is certain that the problem lies in Access but i'm unsure how that can be the case.
Has anyone experienced this problem before ? Is there a fix and if so how is that implemented ?
I created a database in Microsoft Access and then upsized it to SQL Server afterwards.
I now need to add a new column to a table in the database. So I added the new column in SQL Server using ALTER TABLE command. This column is a foreign key which has an associated parent key in another table which contains a description of the column. e.g. Main_Table Case_ID Foreign_Key_Field_ID 1234 78
New_Foreign_Key_Table Foreign_Key_Field_ID Foreign_Key_Field_Desc 78 Describes the ID 78
I also setup a CONSTRAINTREFERENCE between the fields in these 2 tables.
I then relinked my tables using the Linked Table Manager to pick up the change to the table I altered (Main_Table). I also linked to the new table I created (New_Foreign_Key_Table).
My problem: In Access if I create a new table I can select the lookup value for a field in the Table Design view Lookup tab. However, I altered my table in SQL Server and then linked to it again to pick up the change. Now when I view the linked table in Access it does not have any lookup value AND it's not possible to change this for a linked table.
Is there some piece of SQL I can use in SQL Server to change the lookup value for the field in my table OR have I approached this the wrong way. Should I have modified the local table in Access and upsized it again?
Otherwise if I select all values from my Main_Table in a form it displays the Foreign_Key_Field_ID instead of the Foreign_Key_Field_Desc because the lookup value has not been set to Foreign_Key_Field_Desc
This posting is a bit long - I hope it makes sense.
Any ideas or suggestions would be greatly appreciated.
I'm trying to store files on a linked SQL Server table, much the way I would using the Attachment data type in Access. (I couldn't use the Attachment data type, though, due to size restrictions - we're storing about a gig a week.)
Almost everyone says to store the attachments on a file system and save the location - I can't do that because of varying permissions. The files HAVE to be stored in the SQL Server table for security reasons.
So how do I do it? I've tried converting the files to binary and updating the recordset that way, but it always comes back as null. I'm not sure what I'm doing wrong.
Code: Public Function Test(strFile As String) As Boolean Dim rsAtts As dao.Recordset Dim ifilenum As Double Dim btAR() As Byte Set db = CurrentDb
Hi, My access database's tables are currently linked to an excel document. My question is, how do I move my database to the network? Currently when I move the the database to the network no one can open my tables because the link goes to my documents on my computer which they can't access on the network. So how do I move the database and maintain the link between the database and my tables?
Can someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? I am using Access 2002. I also wonder if someone can tell me how to modify a field in a linked table? I have tried altering and saving the changes, but it won't save them because it's a linked table. Thank you in advance to anybody who can help me out here!
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase and S:datadata.mdb is now residing in O:masterdata and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so, 1. find unlinkable tables 2. search its new location under O:master 3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
I have a database that currently has security in place so that users input their own usernames and passwords to access the database. The users have recently been added to a windows 2000 server and I want to be able to use their windows 2000 server logon credentials to provide them with automatic access to the database. Is there anyone out there who knows how to set this up? It is a rather urgent request if you could get back to me either on this forum or via email
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
I am rebuilding an application for a client and I have an Access table that I am using as a temporary table. Once the user is done entering information into the temporary table through a form, the user presses an update button that appends the records using an Append Query in Access to an SQL Server Table.
The following error message occurs:
"ODBC -- insert on a linked table 'linked tblname' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'linked tblname' when IDENTITY INSERT is set to ON. (#545)
I am using a form, subform combination to record a bill with many details. The bill summary is posted into a tbl_TransactionsMain table in SQL Server using the ADO AddNew method. The PK for the tbl_TransactionsMain is then entered into the temporary table in Access. When the temporary table records are appended into tbl_TransactionDetail the error message occurs.
What is also interesting is while typing out this post I thought to test the error by manually trying to run the query. The query worked like a charm! :confused: When the orginal error occurred off of the form I tried to run the query manually and it failed. I am guessing that this might have something to do with the ODBC timeout.
I think SQL Server/ODBC connection is not liking how I have a set of records in an Access table with foreign key numbers assigned when I am attempting to append the records. I am new to SQL Server and any ideas are most appreciated! :)
I have A db that links to a SQL Server table via an odbc connection. The problem is that I have to set up the odbc datasource on each machine that needs to run the db. Can I set up an ADO connection to the table and use that connection as a record source through out the app to drive forms , reports, etc? (And this would eliminate the need to set up and odbc connection to the table on each machine...) :confused: :confused:
Hi, Current situation: We have various departments using a Db and some of the users have drive mappings to different to others (company reasons). So far we have had to create different front-ends for each section, not very useful for updates etc.
What I'd like: To be able to link to the tables on the server using the server name and not a specific drive letter.
My question: Is this possible some how?
Would using SQL server for the back end be a way of getting round this issue?
I am new to this forum and I am hoping you can help. I have produced an Access 97 database on a PC on the hard drive (C I have sent this to a client but he cannot run it as the backend data files are in C: and he is running the whole access programme on the R: drive. As the front end is an MDE file is there anyway I could write the path to the linked files so that access can find them automatically. The same problem applies with the help files, how can you define the location of the help files when you don't know the end users server location?
