Post reply

Sql Connection for Secured Access Files

  • January 21, 2009 at 8:44 am

    #204400

    Hi All

    I've been trying to create a linked server connection between access and sql for secured access databases (using ms sql server 2008).

    I've used sp_addlinkedserver and sp_addlinkedsrvlogin definitions which would let me open databases with no (*.mdw) work group file protection.What I would like to do is to create a flexible connection which would let me to define the workgroup file inside the definition when i need.

    That ,should not need me to make changes in system registary as i'm using this prosedure in my windows application and don't want to make changes in someone else's registary.Besides the access may not be installed to the source computer.

    It occured strange to me that i can create a connection with Microsoft Jet OLE DB 4.0 and define mdw inside like:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:System Database=system.mdw;User ID=myUsername;Password=myPassword;

    but can not fit it in linked server command.At least i could not 🙁

    İn conclusion, i'm trying to add the mdw file definition inside of my sql command if possible.That's all.

    Thanks

  • January 22, 2009 at 4:11 am

    #930987

    What I was using:

    --Dropping linked server if existed

    IF EXISTS

    (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'LNKEDSERV')

    EXEC master.dbo.sp_dropserver @server=N'LNKEDSERV', @droplogins='droplogins'

    go

    --Creating a new linked connection

    EXEC sp_addlinkedserver

    @server =N'LNKEDSERV',

    @srvproduct = 'OLE DB Provider for Jet',

    @provider = 'Microsoft.Jet.OLEDB.4.0',

    @datasrc = N'C:\SampleFile1.mdb'

    go

    EXEC sp_addlinkedsrvlogin

    @rmtsrvname=N'LNKEDSERV',

    @useself=N'False',

    @locallogin=NULL,

    @rmtuser=N'admin',

    @rmtpassword=''

    go

    --Listing the first row

    SELECT TOP 1 *

    FROM [LNKEDSERV]...[Data]

    go

    Now ,this is working fine for non-secured mdb files.You may notice that the user name is admin and password is blank as these are the default values that access create.

    However if i want to link a database using a workgroup file which has spesific username and password ,i can not create the link.Which is normal because programatically i should point the workgroup file as i point the datasource in C:\SampleFile1.mdb.Naturally only by then, the password and username will be accepted.

    So ,i'm stuck to pointing the workgroup file.Access is not installed so no registary and default file change is useful.

    Any ideas how i can add to point the workgroup file in code?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply