Don't want Trusted Connection option...

Hi,

Weird question, but hey!!

We have mixed-mode authentication on one of our databases.

Is there any way of avoiding the users seeing the "Use trusted connection" option when in the connection dialog (eg from Excel Import Data)

I would like to enforce use of the Username/Password option if possible.

Otherwise, I'm faced with implementing user-level SQL Server security & Windows authentication mode for an application which has, up until now, run as the only available app in a very restricted desktop environment.

All suggestions gratefully received.

TIA


SteveYou can provide a little form and ask for UID and PWD, use them in connect string instead of doing nothing and letting system decide what to do, in this case it provides generic login dialog box.

Don't want Trusted Connection option...

Hi,
Weird question, but hey!!
We have mixed-mode authentication on one of our databases.
Is there any way of avoiding the users seeing the "Use trusted connection"
option when in the connection dialog (eg from Excel Import Data)
I would like to enforce use of the Username/Password option if possible.
Otherwise, I'm faced with implementing user-level SQL Server security &
Windows authentication mode for an application which has, up until now, run
as the only available app in a very restricted desktop environment.
All suggestions gratefully received.
TIA
SteveSteve,
First of all security level is set at server level not per database. If I
understood you correctly, you want to use SQL Authentication for one
database and keep mixed mode for all other database
To stop user connecting to database using Trusted connection.
Deny BULITIN\Administrator account
make sure no windows user has permission on that database
create sql user account in that database
create dsn to use the new user name/password and use the new dsn to connect
to sql server
VT
"CyberDwarf" <steve.ingle@.furlongsolutions.com> wrote in message
news:Ory%23$IDIHHA.4804@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Weird question, but hey!!
> We have mixed-mode authentication on one of our databases.
> Is there any way of avoiding the users seeing the "Use trusted connection"
> option when in the connection dialog (eg from Excel Import Data)
> I would like to enforce use of the Username/Password option if possible.
> Otherwise, I'm faced with implementing user-level SQL Server security &
> Windows authentication mode for an application which has, up until now,
> run as the only available app in a very restricted desktop environment.
> All suggestions gratefully received.
> TIA
>
> Steve
>|||Thanks VT.
I was already working down that route, but it's nice to have my reasoning
confirmed
Steve|||You may be interested in the route we have taken...
a.. Create a project custom property to hold the (encrypted) connection
string;
b.. In VBA code, ensure that Persist Security info is turned OFF;
c.. Make sure sure that all Windows users have no rights to access SQL
Server (ie, they won't be able to use a Trusted Connection);
d.. When the ADE starts, we look up the required connection info from the
custom property, connect to SQL Server using the required access
permissions, then remove the Persist Security setting (the project loses the
password);
e.. At this point we have our required connection and no-one else can
access the backend via our ADE, unless they know the admin logon &
password).
That's basically it!
We've done basic testing & it seems to work...

Domain Trust - login failed: not a trusted sql connection

(Cross post from newsgroup)

Attempting to implement Windows authentication between trusted domains. . .

I have a domain trust set up between two domains connected via persistent vpn:
REMOTE and HQ

sqlserv is: HQ\SQLSERV (SQL 2000 server)

client machine is: REMOTE\MACHINE1 (SQL 2005 tools)

I sucessfully added user REMOTE\User1 to system admins on HQ\SQLSERV I can login via Remote Desktop from REMOTE\MACHINE1 to HQ\SQLSERV and open SQL2000 Query Analyzer and query databases as REMOTE\User1

When running SQL2005 SQL Server Management Server on REMOTE\MACHINE1 logged on as REMOTE\User1
I cannot connect to HQ\SQLSERV.
I get:

TITLE:
Connect to Server
Cannot connect to HQ\SQLSERV.
ADDITIONAL INFORMATION: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476
BUTTONS: OK

Any ideas? What I am trying to do should be possible. Isn't it?

Check your connection info in SSMS on REMOTE\MACHINE1
Check you have all the required DNS
Try osql -SSQLSERV -E|||

Mulhall wrote:

Check your connection info in SSMS on REMOTE\MACHINE1

fails on both named pipes and tcp/ip

Check you have all the required DNS

It appears the DNS replicates sucessfully as I have set up secondaries on both sides for the other domains.
I can ping from one side to the other.
I can assign rights from one side to the other, etc. . .

Try osql -SSQLSERV -E

same error|||

Ahhh. . .

found the problem. . .

forgot that we had moved all of our workstations into sets of ADS folders, one for general all purpose use where access via network was permitted to all authenticated users and one set that had access permissions specifically defined to only developers.

Had to give access to the remote developers group via group policy.
The easiest thing was -

Add a Local Domain Group on HQ - Remote Developers
Add REMOTE\Developers to HQ\Remote Developers
Add HQ\Remote Developers to 'Allow access from network' on the applicable gpo

Does that make sense?

Well, it works!!!

You know. . .
Somethings M$ does really really well - Active Directory/Policy Management/Name Service Administration, SQL Server!!!

Somethings they choke on - Visual Studio 6 (oh what a dog!)

At any rate, setting up this VPN and Domain trust was rather simple and straight forward!

Domain Groups and Windows Authentication

Hi

We are planning implementation of a currently Sybase db. The users (about 3600) will be i 5 domains and we want single sign-on through trusted connections. We want to use the database roles to define different user access on databases and tables. There will be around 2000 roles. We also want to add the users directly to the database roles without having to grant each user database access.

So I thought that I could add the user groups from all domains and then add each domain user account to specified database roles. Am I right here or what? The Windows authentication will lookup or check the users kerberos ticket during logon process and allow logon.

The documentation here is weak and I assume it's a windows authentication question but wondered if any of you guys had been down the same road.

For creating the groups I have the following options:

Create a domain group and put all the usergroups from the other domains in this group

Add user groups from all other domains directly into the SQL Server.

Any recommendations here?

YOu don′t need to add the single users to the database. If you put the domain groups (which contain the users) to the server and the database principles, SQL Server will take care of doing the authentication.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

HI,

If you are adding your domain groups to your SQL Server and grant them to allow access of DB/objects then Windows Server will take care of logins to SQL Server tooo do not need to do anything separately.