Blocking MS Access from linking tables...

Good morning...
I have an Access front end that uses SQL Server linked tables. SQL Server
uses Windows authentication. I have one Windows group that all Access users
are a member of. I added that group to SQL Server logins and gave it
public, datareader, and datawriter rights to the one database that's used.
My front end is locked down, but I want to stop users from creating a new
.mdb and linking SQL Server tables through DSNs or ADO connections or even
just importing the links from the actual front end.. I've tried setting the
"denydatareader" security policy - that keeps the SQL tables from being seen
in the import/link list- but also blocks read rights from the actual front
end database. I could set an Access database password on the front end to
block importing the links, but that only solves one of the three problems
and I want to stay away from Access security altogether.
Is there a way to stop users from creating their own DSNs or connection
objects or linking tables while still using Windows authentication?
Thanks.
Matthew Wells
MWells@.FirstByte.netNo. If you're using Windows authentication, and have granted
users/roles permissions on the base tables, then they can get at the
data no matter what tool they use. An alternative would be to revoke
permissions to public on the tables, and create an Access application
that does not use linked tables, but instead uses pass-through queries
to execute stored procedures. This is a lot more work since you'll
need to create an unbound FE, but it can be done. Only users who are
comfortable working with stored procedures would be able to get at the
data. Another option would be application roles, but they are a really
poor choice for linked table apps. see
http://support.microsoft.com/defaul...;EN-US;Q229564.
--Mary
On Thu, 28 Oct 2004 13:54:05 GMT, "Matthew Wells"
<MWells@.FirstByte.net> wrote:

>Good morning...
>I have an Access front end that uses SQL Server linked tables. SQL Server
>uses Windows authentication. I have one Windows group that all Access user
s
>are a member of. I added that group to SQL Server logins and gave it
>public, datareader, and datawriter rights to the one database that's used.
>My front end is locked down, but I want to stop users from creating a new
>.mdb and linking SQL Server tables through DSNs or ADO connections or even
>just importing the links from the actual front end.. I've tried setting th
e
>"denydatareader" security policy - that keeps the SQL tables from being see
n
>in the import/link list- but also blocks read rights from the actual front
>end database. I could set an Access database password on the front end to
>block importing the links, but that only solves one of the three problems
>and I want to stay away from Access security altogether.
>Is there a way to stop users from creating their own DSNs or connection
>objects or linking tables while still using Windows authentication?
>Thanks.
>Matthew Wells
>MWells@.FirstByte.net
>|||I read that article. It seems to apply only to ADO conenctions. Aren't
linked tables DAO? Does connection pooling work the same way? This is a
database that was converted from Access to SQL Server. We have to lock down
the data from any outside attempts to get it. I don't want to use SQL
authentication because I don't want to maintain two sets of security logins.
I know that using an Access form can create multiple SPIDs on SQL Server
(combo box rowsources et al). What is the downside of using Application
Roles?
Thanks.
Matthew Wells
MWells@.FirstFleet.com
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:vh62o051su3gi6fsaljgvmh1me12b8fu8p@.
4ax.com...
> No. If you're using Windows authentication, and have granted
> users/roles permissions on the base tables, then they can get at the
> data no matter what tool they use. An alternative would be to revoke
> permissions to public on the tables, and create an Access application
> that does not use linked tables, but instead uses pass-through queries
> to execute stored procedures. This is a lot more work since you'll
> need to create an unbound FE, but it can be done. Only users who are
> comfortable working with stored procedures would be able to get at the
> data. Another option would be application roles, but they are a really
> poor choice for linked table apps. see
> http://support.microsoft.com/defaul...;EN-US;Q229564.
> --Mary
> On Thu, 28 Oct 2004 13:54:05 GMT, "Matthew Wells"
> <MWells@.FirstByte.net> wrote:
>
Server[vbcol=seagreen]
users[vbcol=seagreen]
used.[vbcol=seagreen]
even[vbcol=seagreen]
the[vbcol=seagreen]
seen[vbcol=seagreen]
front[vbcol=seagreen]
to[vbcol=seagreen]
>|||Yes, connection pooling works the same way. If you are serious about
locking down the SQL Server database, then DO NOT use Access as a
front-end unless you use it in an unbound scenario as I described
below. You will need to revoke or deny permissions on the tables and
create parameterized stored procedures for all DML operations so that
users can interact with the data only through your stored procedures,
which are executed using a least-priviledged account.
Application roles are intrinsically insecure because you must store
the password that activates them on the client, where it can be
discovered by a determined attacker. If you create an unbound
application that executes under least priviledges, then there likely
won't be much penalty for using them (other than performance) or much
harm if the password is uncovered, but then there's not much benefit,
either. Using Windows authentication is more secure than SQL logins,
and if all users belong to roles that have extremely restricted
permissions that only allow them to execute parameterized stored
procedures, then that's about the best you can do. You can also
provide additional verification and validation in your stored
procedure code (for example, only allowing users to access rows that
they "own").
There is code and discussion of the techniques involved in writing an
unbound Access application in the book described in my sig. It's a lot
of work, but if security is a priority then you have to do it. Access
was designed to be easy to use, not secure.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 01 Nov 2004 14:29:21 GMT, "Matthew Wells"
<MWells@.FirstByte.net> wrote:

>I read that article. It seems to apply only to ADO conenctions. Aren't
>linked tables DAO? Does connection pooling work the same way? This is a
>database that was converted from Access to SQL Server. We have to lock dow
n
>the data from any outside attempts to get it. I don't want to use SQL
>authentication because I don't want to maintain two sets of security logins
.
>I know that using an Access form can create multiple SPIDs on SQL Server
>(combo box rowsources et al). What is the downside of using Application
>Roles?
>Thanks.
>Matthew Wells
>MWells@.FirstFleet.com
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:vh62o051su3gi6fsaljgvmh1me12b8fu8p@.
4ax.com...
>Server
>users
>used.
>even
>the
>seen
>front
>to
>

Blank password check

Is there any way to check whether the password for existing sql authentication login is blank? (except trying to connect)

Where can I find information about how the passwords are stored internally in SQL Server?

which version r u talking about. if its 2005 you can do that i believe.

credentials are stored in sysxlogins system table

Madhu

|||Only hashes are stored, you won′t find them in clear text. For SQL Server 2005 use password policies to ensure a certain password complexity.

Jens K. Suessmeyer:

http://www.sqlserver2005.de

|||Yes I understand it, I just wonder how some tools discover logins with blank passwords (http://www.ngssoftware.com/products/database-security/ngs-squirrel-sql.php)|||

Oh, actually it seems to be fairly simple,

I just call ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N'' and if it succeed -- login myLogin has a blank password. Or am I missing something?

|||

sir..... you are resetting the password to null. it will change the password, it will not tell u whether its blank or not. But it will make the password blank evenif its not blank. Not atall the correct method

Madhu

|||No, trying to change the password seems to be a bit offensive :-)

I wrote this short code snippet for you, tell me if it works for you, it scans all reachable /discoverable servers and test all SQL Server logins:

Code Snippet

foreach (DataRow dr in SmoApplication.EnumAvailableSqlServers().Rows)

{

Server srv = new Server(dr["Name"].ToString());

foreach (Login l in srv.Logins)

{

if (l.LoginType == LoginType.SqlLogin)

{

SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User Id={1},Password={2}",

srv.InstanceName,

l.Name,

string.Empty));

try

{

conn.Open();

Console.WriteLine(string.Format("The userid {0} has an empty password.", l.Name));

}

catch (SqlException ex)

{ }

finally

{

if (conn.State == ConnectionState.Open)

conn.Close();

}

}

}

}

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jens,

the way you suggest works fine I think.

|||

Madhu,

I cannot agree with you.

See the following script:

Code Snippet

CREATE LOGIN myLogin WITH PASSWORD = N'IT IS NOT A BLANK PASSWORD!', CHECK_POLICY = OFF

BEGIN TRY

-- this statement will FAIL because we specified invalid old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

END TRY

BEGIN CATCH

PRINT 'Password is NOT blank!'

END CATCH

-- this statement will actually change the password

ALTER LOGIN myLogin WITH PASSWORD = N''

PRINT '//Password was changed to blank//'

-- so now password is blank

BEGIN TRY

-- this statement will SUCCEED because we specified right old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

-- but actually nothing will happen because new password is the same as old one.

PRINT 'Password is BLANK!'

END TRY

BEGIN CATCH

END CATCH

DROP LOGIN myLogin

The idea is that ALTER LOGIN fails when called with OLD_PASSWORD option and invalid old (current) password given.

I think that it is even more effective way to check than trying to logon on server with <loginName, blankPassword> credentials.

Blank password check

Is there any way to check whether the password for existing sql authentication login is blank? (except trying to connect)

Where can I find information about how the passwords are stored internally in SQL Server?

which version r u talking about. if its 2005 you can do that i believe.

credentials are stored in sysxlogins system table

Madhu

|||Only hashes are stored, you won′t find them in clear text. For SQL Server 2005 use password policies to ensure a certain password complexity.

Jens K. Suessmeyer:

http://www.sqlserver2005.de

|||Yes I understand it, I just wonder how some tools discover logins with blank passwords (http://www.ngssoftware.com/products/database-security/ngs-squirrel-sql.php)|||

Oh, actually it seems to be fairly simple,

I just call ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N'' and if it succeed -- login myLogin has a blank password. Or am I missing something?

|||

sir..... you are resetting the password to null. it will change the password, it will not tell u whether its blank or not. But it will make the password blank evenif its not blank. Not atall the correct method

Madhu

|||No, trying to change the password seems to be a bit offensive :-)

I wrote this short code snippet for you, tell me if it works for you, it scans all reachable /discoverable servers and test all SQL Server logins:

Code Snippet

foreach (DataRow dr in SmoApplication.EnumAvailableSqlServers().Rows)

{

Server srv = new Server(dr["Name"].ToString());

foreach (Login l in srv.Logins)

{

if (l.LoginType == LoginType.SqlLogin)

{

SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User Id={1},Password={2}",

srv.InstanceName,

l.Name,

string.Empty));

try

{

conn.Open();

Console.WriteLine(string.Format("The userid {0} has an empty password.", l.Name));

}

catch (SqlException ex)

{ }

finally

{

if (conn.State == ConnectionState.Open)

conn.Close();

}

}

}

}

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jens,

the way you suggest works fine I think.

|||

Madhu,

I cannot agree with you.

See the following script:

Code Snippet

CREATE LOGIN myLogin WITH PASSWORD = N'IT IS NOT A BLANK PASSWORD!', CHECK_POLICY = OFF

BEGIN TRY

-- this statement will FAIL because we specified invalid old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

END TRY

BEGIN CATCH

PRINT 'Password is NOT blank!'

END CATCH

-- this statement will actually change the password

ALTER LOGIN myLogin WITH PASSWORD = N''

PRINT '//Password was changed to blank//'

-- so now password is blank

BEGIN TRY

-- this statement will SUCCEED because we specified right old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

-- but actually nothing will happen because new password is the same as old one.

PRINT 'Password is BLANK!'

END TRY

BEGIN CATCH

END CATCH

DROP LOGIN myLogin

The idea is that ALTER LOGIN fails when called with OLD_PASSWORD option and invalid old (current) password given.

I think that it is even more effective way to check than trying to logon on server with <loginName, blankPassword> credentials.

Blackberry Server MSDE and active directory authentication

To all, thanks in advance,
I have blackberry server running sql msde 2000 (sp4), which is
authenticated via windows integrated with an active directory user
account. When I changed the password on said user account, restart the
mssqlserver service, it fails to start with a 560 error. I had to
changed said user account back to the old password in order for the
mssqlserver service to start. I wanted know where in ms sql msde 2000,
I needed to make the change in order for this service to start. I also
tried starting the service using local system account and it would not
start either. All help with this is greatly appreciated.
JB
Hi
Control Panel > Services. The Service can be stopped and started from there,
and the account password can be changed there for the service.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jon.jefferson@.gmail.com> wrote in message
news:1131849352.800496.311170@.g47g2000cwa.googlegr oups.com...
> To all, thanks in advance,
> I have blackberry server running sql msde 2000 (sp4), which is
> authenticated via windows integrated with an active directory user
> account. When I changed the password on said user account, restart the
> mssqlserver service, it fails to start with a 560 error. I had to
> changed said user account back to the old password in order for the
> mssqlserver service to start. I wanted know where in ms sql msde 2000,
> I needed to make the change in order for this service to start. I also
> tried starting the service using local system account and it would not
> start either. All help with this is greatly appreciated.
> JB
>
|||Mike
Thanks for your response, but I did that and the service would not
start under with the new password. The only idea I did not try was to
restart the Server.
Thanks,
JB
|||Mike,
Also, there are other services that start with this same service
account (i.e. all the blackberry services,) and those start fine,
except for errors in the Apps event log because the msssqlserver
service would not start with the same user account change for some
strange reason. I am changing passwords to in AD because I am taking
over duties after someone has left and need to change passwords for
security purposes. This is my only hold up.
JB