none
Borland C++ Delphi application is not able to connect to SQL Server as Non Admin user

    Question

  • Hi,

    I am working on a Borland C++ and Delphi application. This application connects to  SQL Server and returns database queries results. Once we migrated to windows Server 2003 we have started facing an issue.

    1. IF we login to client application as admin user then this connects properly with SQL Server and returns database queries.But if we connect using Non- Admin users then application is not able to connect SQL Server.

    SQL Server 2008

    Windows Server 2003.

    Can anybody help on this.

    Thanks in Advance.

    Rakesh

    Tuesday, January 28, 2014 5:38 PM

All replies

  • Please define "login". What connection string do you use?
    Tuesday, January 28, 2014 6:13 PM
  • Hello Rajesh,

    It's because only the admin do have permission to logon to SQL Server / accessing the database, but not the common user. You have to add the Windows account as login and grant permissions to access the database / data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, January 28, 2014 6:45 PM
  • Hello,

    Agree with Olaf.
    You should create a login from the non-admin account and grant access premission to the database which you want to get the data.
    For example:

    --create login 
    create login [<domainName>\<login_name>] from windows;
    --create database user for the login 
    use DB_name ;
    go
    create user [<domainName>\<login_name>]
    go 
    --grant premission 
    EXEC sp_addrolemember 'db_datareader', '<domainName>\<login_name>'
    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Wednesday, January 29, 2014 8:15 AM
    Moderator
  • This application is written in Borland C++ and Delphi running on Windows 2003 Server. This  is a Windows GUI based small tool. Basic functionality of the tool is : 1) Once we invoke the tool it connects to DB(SQL Server 2005) and populates a list of all the tables present in DB.   2) If we select any table from the list populated in step (1) and provide query parameters, this will return results from Database.

    Issue:  IF we login to the Windows machine as Administrator this application runs perfectly .This application populates the DB tables correctly and returns results from database when queries executed as in step (2) But if we login as some other user (like hubapp) then this populates some different set of table which are not from the same database instance .Also the queries does not return any results. In the logs we get following error message : (TiltData is the instance name for our Database)

    Msg:Application Exception : Cannot locate or connect to SQL server.Unable to connect: SQL Server is unavailable or does not exist.  Specified SQL server not found.Alias: TiltData1 occurred in main thread.

    As for the connection string 

          'DATABASE NAME=tiltdata'
          'SERVER NAME=SDV3'
          'USER NAME=sa'
          'OPEN MODE=READ ONLY'
          'SCHEMA CACHE SIZE=8'
          'BLOB EDIT LOGGING='
          'LANGDRIVER='
          'SQLQRYMODE=SERVER'
          'SQLPASSTHRU MODE=NOT SHARED'
          'DATE MODE=0'
          'SCHEMA CACHE TIME=-1'
          'MAX QUERY TIME=300'
          'MAX ROWS=-1'
          'BATCH COUNT=612'
          'ENABLE SCHEMA CACHE=FALSE'
          'SCHEMA CACHE DIR='
          'HOST NAME='
          'APPLICATION NAME=QueryTool'
          'NATIONAL LANG NAME='
          'ENABLE BCD=FALSE'
          'TDS PACKET SIZE=65535'
          'BLOBS TO CACHE=64'
          'BLOB SIZE=32'
          'PASSWORD=jstart')

    Any help is appreciated. I have spent a lot of time on this issue with no results.

    Regards

    Rakesh

    Wednesday, January 29, 2014 8:11 PM
  • My ADOConnection.ConnectionString:
    Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=False;User ID=sqlUser;Initial Catalog=DataBase;Data Source=SQLServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PCName;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False;

    At the SQL server I created a user group with read and write permissions; each Windows user was added to that group

    ADOConnection component does not save the password when you use Native Client Provider. It saves password for OLE db provider

    (Sorry for my english language)

    Regards

    http://www.connectionstrings.com/sql-server-native-client-10-0-oledb-provider/ 



    Friday, January 31, 2014 2:53 PM