September 24, 2003 at 10:10 am
Hi,
The company I work for (as consultant) has several older apps (Delphi & VB) accessing different SQL db's (SQL 7/8). The db's were all setup with "sa" access !!!! (probably to make it easier for the developer). I've to write new front-ent apps for the existing tables/dbs, but want to get rid of this "sa" access. Is it possible to introduce NT security authentication, still keeping the "sa" access to ensure the older apps would still work ? The IDE that I use to develop the new app can access the data thru an odbc link, but seems to fail due to improper authentication ... (and doesn't allow to enter any "sa"-like values).
Thanks in advance for any tips.
Beat BUCHER
++++++++++
Beat BUCHER
SysAdmin
Dynamics GP Consultant
++++++++++
September 24, 2003 at 10:15 am
You can introduce NT auth, but this is Per connection. Each connection can be set to connect with different access. How are you connecting with the IDE. Are you using a DSN or declaring the connection string?
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 24, 2003 at 10:22 am
Use Enterprise Manager, drill down to Security, right click on Logins, select New Login. Fill out the information.
On the General tab is where you set the LOGIN name, type of authentication, password, default database and language.
On the Database Access tab is where you can set which databases the login has access to and what type of access.
On the System Access tab is where you give SysAdmin (SA), etc. privileges.
-SQLBill
September 24, 2003 at 10:24 am
Hi Steve,
You can introduce NT auth, but this is Per connection. Each connection can be set to connect with different access. How are you connecting with the IDE. Are you using a DSN or declaring the connection string?
for the connection, I've to setup an odbc link with odbcad32, that points to the desired db on the sql server. Even if I setup the link with the SQL auth ("sa"), it wont accept to connect. On an other odbc link which uses NT authentication, the connection works fine... so I assumed that the problem is the SQL authentication.
Regards,
-------------------
Beat BUCHER
SysAdmin Consultant
++++++++++
Beat BUCHER
SysAdmin
Dynamics GP Consultant
++++++++++
September 24, 2003 at 10:25 am
That's the server side. You add your users as NT Auth, include rights for the databases/objects. These should match which rights you need for access. If you are having issues iwth permissions (object does not exist, or incorrect permissions) you change it here.
If you are having login failed errors, then you might need to reconfigure your client side.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 24, 2003 at 10:29 am
Thanks Bill and Steve,
I'll try this out with EM. The error I get from the IDE is "unable to open the Database". So I really guess it's related to the authentication mode. I'll keep you informed about the results soon as I'm able to test it.
Thanks a lot.
-------------------
Beat BUCHER
SysAdmin Consultant
++++++++++
Beat BUCHER
SysAdmin
Dynamics GP Consultant
++++++++++
September 24, 2003 at 12:17 pm
One other place you need to check. In Enterprise Manager, right click on your server. Select Properties. Go to the Security tab. What type of authentication does the Server permit?
1st you need to set the authentication the server will use. This is the step above.
2nd you need to set how each login (user) with authenticate to access the server. This is the step in my previous post (Security>Logins).
-SQLBill
October 1, 2003 at 11:08 am
Hi Bill,
I finally got my SQL EM console installed on the client pc and was able to browse the server's Databases. I checked the securtiy tab of the server, and it's mixed authentication (SQL and Windows). As soon as I added a regular domain user (and not a local SQL user, like it was before), gave him database permit and added the user into the dt_datareader role of the concerned DB, I was in business 🙂
Thanks a lot for your tips and hope to read you again soon.
-------------------
Beat BUCHER
SysAdmin Consultant
++++++++++
Beat BUCHER
SysAdmin
Dynamics GP Consultant
++++++++++
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply