Transact SQL stuff 2

[ccampbell]

I need to be able to create a new database and restore a dbk file from the command line. when I go to run OSQL it gives me the error "Login failed for user ... Reason: Not associated with a trusted SQL Server connection." when i get the password prompt it am just hitting enter (blank password). My database is just using windows authentication mode, so there shouldn't be a password should there. If so, how can I create a trusted SQL server connection? thanks for you help

 

[tlbroadbent]

The NT login on the machine where you run OSQL must be granted SQL Server access. Use Enterprise manager or the sp_grantlogin stored procedure to grant SQL login permission to the NT login.

EXEC sp_grantlogin 'domain\ntlogin'

You'll aslo want to grant datbase access.

EXEC sp_grantdbaccess 'domain\ntlogin', 'dbuser' Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ccampbell]

Thanks, I ran both of those from the Query Analyzer but I still get the same error message. I have even tried running osql -U<user name> -P<password> and that still give me the same message. Am I missing something? BTW, thanks for the speedy reply. I really appreciate it. If you have any ideas or suggestions on how I can go about creating a new database and restoring a dbk file to create all the indexes, tables, and restore data, please let me know as well. Even if you know of some good websites that I can go to that will help me learn all this stuff, that would be great as well.

 

[tlbroadbent]

Can you post the entire OSQL command line? We may be able to find a problem more easily.

Which version of SQL Server are you running? What is the source of the backup file? You should be able to easily restore a backup in SQL 7 or 2000 with Enterprise Manager or with the RESTORE command in Query Analyzer.

NOTE: You can only restore SQL Server 7 backups on SQL 7 and SQL 7 and 2000 backups on SQL 2000. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ccampbell]

Here is the line that I am typing from a command prompt
osql -Uclc -PTestPassword2 -Schris
clc is the Windows user name
TestPassword2 is the user password
chris is the name of the server.

I have also tried to do it just using osql on the command line and then when it prompts me for the password I type in the windows user password (TestPassword2). I have also tried it blank and it does not work that way as well.

The reason that I need to create a Db and restore a dbk from the command line is for my install script. I have create a program that updates a database, and as part of the initial installation script I would like to be able to create a Db and restore the most current dbk file. In order to do that in the installation script, I believe that I have to do all that from the command line. If you have any other suggestions on how to do that, please let me know. I want there to be virtually no user intervention. I want to be able to set up the name of the database and the size and all that from the installation script. Even if you do not know how to do it from the command line, if you have any ideas or suggestions, I am open to those as well. Examples and web sites would also be very helpful. Thanks again.

 

[aalmeida]

Think your Problem is because you are trying to login twice, remember that your pc is already loged in, when you send an UID and PSW (osql -Uclc -PTestPassword2 -Schris), the correct would be to use -E for trusted connection therefore your line would look like:
osql -E -Schris.
Al Almeida

 

[ccampbell]

that was it! Thanks for the help. Do you have any ideas on the other question about creating a new Db and restoring a dbk file from the command line? Do I want to use osql for this? can I use osql for this? Thanks again.