May 22, 2012 at 12:41 pm
Hi All, I created a odbc source for a sql database back end I am upsizing an access database. When I created the connection; where it ask "How should SQL Server verify the authenticity of the login id", not thinking; I chose SQL Server Authentication and supplied the login and id for the server. Do I need to go back and change this if I want my users to use the access database that have the backend in SQL? I have not created their logins yet in Sql Server containing the backend.
Thank you
May 22, 2012 at 12:53 pm
slimjen (5/22/2012)
Hi All, I created a odbc source for a sql database back end I am upsizing an access database. When I created the connection; where it ask "How should SQL Server verify the authenticity of the login id", not thinking; I chose SQL Server Authentication and supplied the login and id for the server. Do I need to go back and change this if I want my users to use the access database that have the backend in SQL? I have not created their logins yet in Sql Server containing the backend.Thank you
That depends, does your company have a domain set up? Even if you use Windows Authentication, you will have to add their users as logins. We used to use SQL Authentication and give all our users the credentials of SQLRead or DatabaseNameRead.
Jared
CE - Microsoft
May 22, 2012 at 1:12 pm
Ok. I haven't done this in a while. This is what I'm trying to do. I have created a database in SQL Server 2008. I have created an ODBC connection and test it successfully using SQL Authentication. I want to now upsize the access database. I need to put the access backend with link tables to the SQL Server database on a shared drive. When I create the logins for the users, can I set them up to connect using Windows Authentication even though I set the ODBC to SQL Authentication?
Also, I can remember going to each users machine to setup ODBC Connection and using a file DSN? Like I said; it's been a while.
Thanks
May 22, 2012 at 1:16 pm
Hmm... I'm kind of confused. Is there an application accessing the data? Where did you create the ODBC connection? Are you dropping Access once SQL Server is added or are these working together?
Jared
CE - Microsoft
May 22, 2012 at 1:32 pm
sorry for the confusion. We had been using an Access backend which was on the shared server. I created the odbc connection on my laptop. We are using both SQL backend and an Access frontend. The users will have a copy of the Access frontend on their individual computer with the tables pointing back to the SQL Server. With this said; do I create an ODBC connection on each persons machine so they can access the sql server and can they use windows authentication even though I set my machine for sql server authentication. Also, file dsn?? I remember something to this effect.
Thanks so much
May 22, 2012 at 1:41 pm
So, for them to make a connection to SQL Server, they will need a connection on their machine. Whether or not you can use a file, I don't know. Each connection will have its own properties, so yours with the SQL Server Authentication does not designate that all of them must be authenticated with that. They are each separate. For example, we would use windows authentication for people that were on pc's and the domain, sql authentication for macs and pc's not on the domain.
Jared
CE - Microsoft
May 22, 2012 at 1:53 pm
Thanks! Make sense and starting to come back to me. Stay tuned and Thanks for all you help:)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply