|
|
|
date: Wed, 19 Sep 2007 13:24:00 -0700,
group: microsoft.public.sqlserver.odbc
back
Re: Linking Access 2003 to SQL 2005
(1) Do not ever use the sa login and password for anything. Create a
separate login and grant it only the permissions needed. If you've
gone to all the trouble to move the data to SQL Server, then you
probably don't want to jeopardize it by putting the entire server at
the mercy of ignorant (or malicious) users.
(2) Do not use a DSN. DSNs are a pain to create and adminster in
addition to being a security vulnerability. Connect using an ODBC
connection string at runtime instead. If you are using linked tables,
drop the links and recreate them using this connection string. You can
do this using VBA/DAO code that sets properties of TableDef objects
(you can also manipulate DAO.QueryDef objects programmatically).
-Mary
On Wed, 19 Sep 2007 13:24:00 -0700, BackthePack
wrote:
>I have read numerous posts on this topic, but to no avail. I have several
>Windows XP Pro desktops (SP2) and a new server running Server 2003 (SBS) with
>the full version of SQL 2005. I have a split MS Access program with the FE
>residing on the desktops and the BE on the server. The Access program gets
>information from a SQL Database, located on the server (which worked fine
>when I was using Access 2000 and Sql Server 2000). There is only one server
>and one domain, so nothing too complicated. The problem I am having is when
>I try to link to the SQL tables. I get
>Connection Failed
>Sql State: '28000'
>Sql Server Error: 18456
>[Microsoft][ODBC Sql Server Driver][Sql Server]Login failed for user ' '
>
>Sql 2005 is configured for Sql Authentication. I set up the File DSN with
>Sql Authentication. I can get it to connect when I enter the sa user name
>and password, but cannot get them embedded anywhere to make the connection
>automatically. I would prefer to not give the sa password to all the users
>of the program, but that is the only way I can do it right now. Any help
>would be appreciated. Thank you,
>
>Jim
>
date: Thu, 20 Sep 2007 17:01:16 -0400
author: Mary Chipman [MSFT]
Re: Linking Access 2003 to SQL 2005
Thank you Mary. Any tips or can you steer me to a decent article as to how
to go about using an ODBC connection string at runtime?
Jim
"Mary Chipman [MSFT]" wrote:
> (1) Do not ever use the sa login and password for anything. Create a
> separate login and grant it only the permissions needed. If you've
> gone to all the trouble to move the data to SQL Server, then you
> probably don't want to jeopardize it by putting the entire server at
> the mercy of ignorant (or malicious) users.
>
> (2) Do not use a DSN. DSNs are a pain to create and adminster in
> addition to being a security vulnerability. Connect using an ODBC
> connection string at runtime instead. If you are using linked tables,
> drop the links and recreate them using this connection string. You can
> do this using VBA/DAO code that sets properties of TableDef objects
> (you can also manipulate DAO.QueryDef objects programmatically).
>
> -Mary
>
> On Wed, 19 Sep 2007 13:24:00 -0700, BackthePack
> wrote:
>
> >I have read numerous posts on this topic, but to no avail. I have several
> >Windows XP Pro desktops (SP2) and a new server running Server 2003 (SBS) with
> >the full version of SQL 2005. I have a split MS Access program with the FE
> >residing on the desktops and the BE on the server. The Access program gets
> >information from a SQL Database, located on the server (which worked fine
> >when I was using Access 2000 and Sql Server 2000). There is only one server
> >and one domain, so nothing too complicated. The problem I am having is when
> >I try to link to the SQL tables. I get
> >Connection Failed
> >Sql State: '28000'
> >Sql Server Error: 18456
> >[Microsoft][ODBC Sql Server Driver][Sql Server]Login failed for user ' '
> >
> >Sql 2005 is configured for Sql Authentication. I set up the File DSN with
> >Sql Authentication. I can get it to connect when I enter the sa user name
> >and password, but cannot get them embedded anywhere to make the connection
> >automatically. I would prefer to not give the sa password to all the users
> >of the program, but that is the only way I can do it right now. Any help
> >would be appreciated. Thank you,
> >
> >Jim
> >
>
date: Thu, 20 Sep 2007 14:16:03 -0700
author: BackthePack
Re: Linking Access 2003 to SQL 2005
You can get the syntax from connectionstrings.com. If you set up
security using SQL logins (instead of Windows logins) then you need to
create a login form that lets the user fill in their name and
password. Here's a code sample to get you started -- you'll need to
modify this to accept input parameters for the login and password if
you don't use integrated security (which is recommended).
Public Sub LinkODBConnectionString()
Dim strConnection As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
' Specify the driver, the server, and the connection
strConnection = "ODBC;Driver={SQL Server};" & _
"Server=(local);Database=SqlDbName;Trusted_Connection=Yes"
' Specifying a SQLS user/password instead of integrated security
' strConnection = "ODBC;Driver={SQL Server};" & _
' "Server=(Local);Database=SqlDbName;UID=UserName;PWD=password"
' Create Linked Table. The LinkedTableName and the
' ServerTableName can be the same.
Set tdf = db.CreateTableDef("LinkedTableName")
tdf.Connect = strConnection
tdf.SourceTableName = "ServerTableName"
db.TableDefs.Append tdf
Set tdf = Nothing
End Sub
On Thu, 20 Sep 2007 14:16:03 -0700, BackthePack
wrote:
>Thank you Mary. Any tips or can you steer me to a decent article as to how
>to go about using an ODBC connection string at runtime?
>
>Jim
>
>"Mary Chipman [MSFT]" wrote:
>
>> (1) Do not ever use the sa login and password for anything. Create a
>> separate login and grant it only the permissions needed. If you've
>> gone to all the trouble to move the data to SQL Server, then you
>> probably don't want to jeopardize it by putting the entire server at
>> the mercy of ignorant (or malicious) users.
>>
>> (2) Do not use a DSN. DSNs are a pain to create and adminster in
>> addition to being a security vulnerability. Connect using an ODBC
>> connection string at runtime instead. If you are using linked tables,
>> drop the links and recreate them using this connection string. You can
>> do this using VBA/DAO code that sets properties of TableDef objects
>> (you can also manipulate DAO.QueryDef objects programmatically).
>>
>> -Mary
>>
>> On Wed, 19 Sep 2007 13:24:00 -0700, BackthePack
>> wrote:
>>
>> >I have read numerous posts on this topic, but to no avail. I have several
>> >Windows XP Pro desktops (SP2) and a new server running Server 2003 (SBS) with
>> >the full version of SQL 2005. I have a split MS Access program with the FE
>> >residing on the desktops and the BE on the server. The Access program gets
>> >information from a SQL Database, located on the server (which worked fine
>> >when I was using Access 2000 and Sql Server 2000). There is only one server
>> >and one domain, so nothing too complicated. The problem I am having is when
>> >I try to link to the SQL tables. I get
>> >Connection Failed
>> >Sql State: '28000'
>> >Sql Server Error: 18456
>> >[Microsoft][ODBC Sql Server Driver][Sql Server]Login failed for user ' '
>> >
>> >Sql 2005 is configured for Sql Authentication. I set up the File DSN with
>> >Sql Authentication. I can get it to connect when I enter the sa user name
>> >and password, but cannot get them embedded anywhere to make the connection
>> >automatically. I would prefer to not give the sa password to all the users
>> >of the program, but that is the only way I can do it right now. Any help
>> >would be appreciated. Thank you,
>> >
>> >Jim
>> >
>>
date: Fri, 21 Sep 2007 09:35:53 -0400
author: Mary Chipman [MSFT]
|
|
